Macro for filter deselection

June 7, 2016 at 15:08:34
Specs: Windows 7
I have a column with data that contains property addresses and when there is no address it lists "no address". I tried using the macro recorder to deselect the "no address" values but when I look at the VB code it selects all the addresses rather than code to deselect the "no address" value. what is the correct code to remove values="no address" in a filter in column AH?

See More: Macro for filter deselection

Reply ↓  Report •

June 7, 2016 at 17:23:51
I'm confused. Please keep in mind that we can't see your workbook from where we're sitting, so it's a little difficult (at least for me) to understand what you are trying to do.

You mentioned a "filter". What kind of filter? Is it an Advanced Filter? A filter that uses criteria? Something else?

I also don't understand what process you followed to record a macro while "deselecting" values.

Could you explain what you are trying to do in a little more detail? If you think that posting some example data will help, please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

June 7, 2016 at 18:18:36
Sorry DerbyDad03 for the confusion.
So I have data in columns, column a= address, column b= city, column c=property type, I then added a basic filter from the data menu.
I then started recording and clicked on the pull down for the filter and deselected the "no address" value from the address column (a), I then stopped the recording and reviewed the macro and saw that instead of the VB code removing the value "no address" it added all the other the addresses as the selection criteria. The problem is when I add new data and run the macro it will fail because instead of deselecting the value "no address" it is going to select these addresses that will more likely not be there with new data, see below:

ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=1, Criteria1:=Array( _
"123 main st", "245 john st", "54 collins rd", "55 west tyler st", _
"850 N dewey ave"), Operator:=xlFilterValues
End Sub

message edited by mecerrato

Reply ↓  Report •

June 7, 2016 at 19:07:40
I can't test this right now, but try using this as Criteria1:

Criteria1:="<>no address"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Reply ↓  Report •

Related Solutions

Ask Question