Autofilter with VBA

[Jon Melone]

Hi,
I receive a spreadsheet with autofilter already enabled. My task is to apply a filter and then enter a vlookup formula into the last column of the filtered list. The last step is to set the filter to show all records. I'd like to have macro to do the work for me.

How do I manipulate an existing autofilter with VBA?

This is what I get when recording a macro:
ActiveSheet.Range("$A$1:$Y$28225").AutoFilter Field:=8, Criteria1:="Netted"

But I need the range to be dynamic as the number of rows in the spreadsheet I'm sent changes.

After the list is filtered, I need to use VBA to enter a vlookup formula in cell Y2 (will be visible when the filter is applied), which then needs to be copied-down to the end of the filtered list.

Finally, the filter is set back to show all and I copy-and-paste values on the vlookups.

So, in addtion to activating the filter, how do I fill down using VBA? The macro recorder shows Selection.FillDown, but at the moment, it's now working when I insert it into my own code.

Jon

 

[Andrew Poulsom]

For your first question, try:

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=8, Criteria1:="Netted"

If you record a macro while entering the formula manually you will get some VBA code.

 

[Desu Nota from Columbus]

Instead of this: ActiveSheet.Range("$A$1:$Y$28225").AutoFilter

Use: Cells.AutoFilter

so

Dim lrow as Long
lrow = Range("A" & Rows.Count).End(xlUp).Row

Cells.AutoFilter Field:=8, Criteria1:="Netted"

Range("I2:I" & lrow).SpecialCells(xlCellTypeVisible).Formula = "=ENTERYOURVLOOKUPHERE"

Does this work for you?

 

[Jon Melone]

Allright! The filtering and entering of the vlookup is working.

I believe I know how to do the rest.

Thanks!

PS - I'm trying to understand the difference between executing a vlookup against a different workbook when the workbook is already open or closed. As far as I can tell, both approaches work, but the former is much, much faster.

 

[Greg Truby]

@ Desu

FWIW - I would swear that the SPECIALCELLS bit used to be necessary. But anymore it's extraneous.

Range("I2:I" & lrow).SpecialCells(xlCellTypeVisible).Formula = "ENTERYOURVLOOKUPHERE"

and

Range("I2:I" & lrow).Formula = "ENTERYOURVLOOKUPHERE"

both yield the same results in Excel 2010.

 

[p45cal]

You say the sheet already has Autofilter applied when you get it, so there's a (hidden system) range called
_FilterDatabase
on that sheet, but this includes the headers of the filter.
This range can also be referred to using
ActiveSheet.AutoFilter.Range
so:

Sub blah()
With ActiveSheet.AutoFilter.Range
' With ActiveSheet.Range("_FilterDatabase")
    .Columns(.Columns.Count).Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = "=VLOOKUP(R[-4]C[1],R16C1:R40C4,3,FALSE)"
End With
'show all:
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

will put the vlookup formula in the last column of the filtered range in only those cells which are showing (xl2010).
Of course, adjust the actual vlookup formula to yours.

 

[Desu Nota from Columbus]

Greg--

Thanks for the info, unfortunately I have only ever used Excel 2003 .

I have excel 2007 at home now, but by the time I get home I am too 'excelled-out' to learn any of the new features. Also the new interface with the menu buttons is so different I get frustrated to be out of my comfort zone.

 

[Jon Melone]

Thanks for the additional approach. The ShowAllData code is what I'll use to wrap it up.

Thanks!
Jon

 

[Greg Truby]

Thanks for the info, unfortunately I have only ever used Excel 2003 .

(@ Desu) You don't need the call to SpecialCells with 2003 either. I remember it being necessary once upon a time, but it's farther back than 2003 and I only have Excel 11, 12 & 14 so I can't test XP or earlier.