VBA Macro to change autofilter based on cell value not working.

[Riley]

Hello,

I am using the following macro to change the autofilter of my data based on a cell value:

Sheets("Summary").Select

ActiveSheet.Range("$A$3:$DT$500").AutoFilter Field:=4, Criteria1:=portsel

For some reason it is "unchecking" the Criteria1 from the filter dropdown list instead of deselecting all the other options.

Please help.

Thanks,
R

 

[Harpreet Singh]

I don't know what you exactly want. You can try this.

Sheets("Summary").Select

ActiveSheet.Range("$A$3:$DT$3").AutoFilter Field:=4, Criteria1:="portsel"

For more clarification, upload relative file.

 

[Hui]

Riley

Can you please post a sample file ?

 

[Riley]

Hi,

Apologies for the confusion. In the attached file, I want to select a port in cell "C2", Sheet "Port Select" and click the "update button" to run the macro, which should filter the data on the "data" tab by the port selected.

Thanks,

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[Harpreet Singh]

Hope that's exactly you want. Please check the attached workbook.

 

[Riley]

Thanks NARAYANK991 & Harpreet Singh! This is exactly what I am looking for. How would I modify the code you gave to exclude blank cells in column B, "Data Tab"?

Regards,

 

[NARAYANK991]

Hi ,

If the blanks are in column B , no modification is required ; any selection , whether it is East , West , North or South , will include only those rows where column B has the selected text ; thus those rows which are blank in column B are automatically excluded.

If you want that blanks in other columns are excluded , then see the attached file.

Narayan

 

[Riley]

Hi Narayan,

Thanks for the quick response. For some reason when I use the original code it returns the following (see attached photo) instead of the desired port.

Regards,

 

[Riley]

Hi Narayan,

I think I was able to figure out the cause of the problem. Thank you all for the help along the way!

Regards,

 

[Riley]

Hi,

I have decided to add some complexity to my example and it is not working as planned. I added filter macros for boat and cargo in the attached example. Unfortunately, when I leave "cargoselect" blank it returns me zero results in my data tab as demonstrated.

Any suggestions?

Thanks,

 

[NARAYANK991]

Hi Riley ,

See the file now.

Narayan

 

[Riley]

Thanks Narayan! That worked like a charm.

Appreciate all of the help.