Problem with Autofilter macro code
 

I have a spreadsheet where columns A:P 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:P

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 B1:P1 so all cells between A1 and Z1 are populated but is there a
better/more reliable way please?

Thanks in anticipation

Problem with Autofilter macro code
 

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


"nospaminlich" wrote:

I have a spreadsheet where columns A:P 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:P

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 B1:P1 so all cells between A1 and Z1 are populated but is there a
better/more reliable way please?

Thanks in anticipation

Problem with Autofilter macro code
 

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