Problem with Autofilter macro code

[Guest]

I have a spreadsheet where columns A are imported unformatted data.

A macro then adds headings in row 1 of columns R:Z and formulas in cells
R2:Z down to the end of the range in A

The result of the formula in column Z is either "" or "Include"

The macro then autofilters on column Z, criteria = "Include" and copies the
resulting data onto a new sheet.

This is the code:

Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="Include"

Most of the time this works but because the cells between B1 and P1 are
blank sometimes the Autofilter starts at col R so Field:=9 is right but other
times it seems to start at say column N so the results are wrong.

An option would seem to be to add another instruction in my code to put ""
in cells B11 so all cells between A1 and Z1 are populated but is there a
better/more reliable way please?

Thanks in anticipation

 

[Allllen]

Range("A1:Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="Include"

 

[Guest]

Thanks Allllen.

Your solution didn't work when I tried it presumably because there is no
data in the range A1:Q5 other than an imported heading in A1.

I put "" in B1:Q1 and then it worked fine so on that basis it looks like I
need to add that to my macro as I suspected.

Thanks again