Specifying range in VBA macros

0
votes

I am really new to VBA. SO this might seem a silly question for experts.

after researching the internet on how to put dynamic range for filters in excel considering a situation when your list might grow or shrink I stumbled on couple of ways. One of them is as the following.

ActiveSheet.Range("A1").AutoFilter Field:=46, Criteria1:="1"

I know the you can put two arguments to specify ranges and the second one is optional. In this particular I am just putting the first cell where my data starts. My questions are below:

  1. does the code automatically select the active range based on the first specified cell?
  2. Had my data started from "C5" would i still have to chose the argument as "A1"?
  3. I am filtering out something from column 46. what does it matter where I specify my first cell? for example if I had chosen "C5" instead of "A1" how it would have affected my macro?
  4. Let's say, I want to filter out something from column 2 ie, Field 2. But the argument for range I choose as "D5". How it would affect my macro code?
1
excelvba
My answer is to find out for yourself! Testing this yourself will allow you to actually see what happens when you tweak the available options available to you from the Range.Autofilter method. While we could show you, doing it yourself is a great learning exp and you are much more likely to remember the results after seeing them. - urdearboy

1 Answers

0
votes

AutoFilter: A Small Study

  • Well, I was curious, too. So I studied it a little bit and created a workbook where you can input the parameters of some of the arguments of the AutoFilter method and just by clicking on a command button apply the changes. The next click removes the filter.
  • The workbook also contains the results of the behavior when changing by row. The same thing could be done by column.
  • Workbook Download (Dropbox)

The following image displays the initial state of the only worksheet in the workbook.

enter image description here

This I was most curious about: what would happen if I would filter in C8. There's a surprise.

enter image description here

There still remains a ton of unanswered questions, but this could get you started to investigate deeper.