Get autofilter to exclude blank cells that contain formulas

[bobert1980]

I have the following line in a macro:

Range("b22:L" & Range("J" & Rows.Count).End(xlUp).Row).AutoFilter

This selects rows based on whether they contain data and creates an autofilter of the range.

The data Im using this on will have blank cells as the bottom rows but they will contain formulas that blank the cells based on ISNA() conditions,

How do I get the above code to only select cells with visible data and ignore those that are blank but contain formulas.

thanks

 

[Jonmo1]

Is there another column that doesn't contain formulas that can be used to determine the lastrow?

 

[bobert1980]

Well column B has no formulas. The other columns have lookup formulas based on column B, if column B is empty the formulas make the cells blank. These are the rows I want to exclude.

The contents of column B are pasted into an area using an advanced filter if that is relevant information.

 

[Jonmo1]

Try

Range("b22:L" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter

 

[bobert1980]

I just spotted that myself but changing it didnt work for some reason

 

[bobert1980]

I found a workaround by just changing the autofilter criteria to exclude blanks like so:

Range("b22:L" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="<>"


thanks for your help

 

[Jonmo1]

Sounds like column B's blanks are not really blank..

Try

Dim lr As Long
lr = Range("B:B").Find("*", Range("B1"), , , , xlPrevious).Row
Range("B22:L" & x).AutoFilter

 

[bobert1980]

Actually this workaround isnt so great if you have data next to the table you want autofilter as that will get hidden along with the blanks.

This means back to square one in finding a selection based solution where the intial range selected to autofilter doesnt include the blank ranges

Another solution is after the macro pastes the column B items the lookup formulas are pasted afterwards and dragged down only to the extent that column B has data.

Eg column Cs lookup is

=IF($B23="","",IF(LEFT($B23)="I",VLOOKUP($B23,exptable,2,0),VLOOKUP($B23,paytable,2,0)))

the data starts in row 23 so the macro will start from B23 and count downwards to see how many cells contain data and then paste the above lookup into that many rows in column C.

How would I do this?

 

[bobert1980]

Your code above does not autofilter and gives the error

Method range of object global failed

any ideas?

 

[bobert1980]

I changed 'x' to 'lr' in your code which implements the autofilter but it still includes blank rows that have formulas