Filter a table using the Same criteria for multiple columns?

0
votes

i have an Excel Tables with 40 columns. Columns F and H:R contains dates. I want to create with VBA the following Filter.

  • If at least one of the date in Columns F and H:R is Tomorrow Date, Keep those rows and Hide the others

I know how to code multiple criteria for the same field....but not one criteria for several fields...Would appreciate any help

1
vbaexcel

1 Answers

1
votes

Create a new column name it ControlColumn and the values of the column are calculated as 

=COUNTIF(A2:G2, TODAY()+1)

see the pic for setupenter image description here

Then use one filter on ControlColumn where value >0 the required range with 

Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$19").AutoFilter Field:=8, Criteria1:=">0", Operator:=xlAnd

enter image description here