Bright01
asked on
Using a Range Name in a Macro
I have a Macro that has the following line it it. I also have a Range Name named CustomerData that is composed of A7:B50.
Macro line of code:
ActiveSheet.Range("$A$7:$B $50").Auto Filter field:=1, Criteria1:=Left(regionArra y(iNextReg ), 2)
End If
End Sub
For this line of code, I'd like to make it so that if I change the size of the range (i.e. by adding another line or two), that the Macro performs against the range and not the A7:B50. How do I code that?
Thank you,
B.
Macro line of code:
ActiveSheet.Range("$A$7:$B
End If
End Sub
For this line of code, I'd like to make it so that if I change the size of the range (i.e. by adding another line or two), that the Macro performs against the range and not the A7:B50. How do I code that?
Thank you,
B.
ActiveSheet.Range(NameofRa nge).AutoF ilter field:=1, Criteria1:=Left(regionArra y(iNextReg ), 2)
ASKER
Please elaborate a little more; Is the Criteria1 because I have two columns or if they are all included already in the range name, may I simply refer to the range name?
Thank you,
B.
Thank you,
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(not for points)
When using an autofilter, you can specify one or two criteria per column. Since you only specify one here, you only see criteria1. It has nothing to do with the size of the range you are filtering.
So
means only show values in the CustomerData range where the first column equals "some value".
When using an autofilter, you can specify one or two criteria per column. Since you only specify one here, you only see criteria1. It has nothing to do with the size of the range you are filtering.
So
ActiveSheet.Range("CustomerData").AutoFilter field:=1, Criteria1:="some value"
means only show values in the CustomerData range where the first column equals "some value".
You can also just use the range name in square brackets:
[CustomerData].AutoFilter field:=1, Criteria1:=Left(regionArray(iNextReg), 2)
As long as you don't have a variable in scope called CustomerData. ;)
ASKER
I'm in the presence of great minds!
So in Microshadow's example for me; what does the Criteria here specify?
ActiveSheet.Range(NameofRa nge).AutoF ilter field:=1, Criteria1:=Left(regionArra y(iNextReg ), 2)
?
So in Microshadow's example for me; what does the Criteria here specify?
ActiveSheet.Range(NameofRa
?
ASKER
Sorry, it wasn't Microshadow's Criteria, it was what I copied for him to look at. Still would like to know what it means.
B.
B.
Hard to tell without seeing the actual file.
Basically, its the two left-most characters of the iNextReg element of the regionArray array.
Basically, its the two left-most characters of the iNextReg element of the regionArray array.
ASKER
Thank you Microshadow! Very helpful. I will be authoring more questions on the particular application since I'm still learning to build it.
Thanks again,
B.
Thanks again,
B.