Advance Filter Last Row

[mdavontuur25]

Hi Excel Gurus

I have been struggling to write this code for weeks.

I want to use advance filter in a macro to extract data in a worksheet that starts with cell B8 (headings is in this row) and it must find the last row of data. eg range = B8:last data row

Currently I have this code

Sub Filter()
Sheets("cbpay").Range("B8:M26").End(xlUp).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1:G1"), Unique:=False
End Sub

As you can see the range is currently just from B8:M26, however sometimes I have 2000 rows and at other times only 4 or 5.

Please help!!!
mOMAn

 

[VoG]

Try

Sub Filter()
Dim LR As Long
With Sheets("cbpay")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B8:M" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1:G1"), Unique:=False
End With
End Sub

 

[JamesW]

Hi,

Is this what you are looking for?

Sub Filter()
    Sheets("cbpay").Range("B8:M" & Range("M" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("A1:G1"), Unique:=False
End Sub

 

[mdavontuur25]

Hi Guys

Thank you for your spedily response how ever on both macros I get errors. John on yours it says missing or illegal field name, and Peter on yours it hi lights LR=... in yellow.

 

[mdavontuur25]

Sorry Peter

Your code worked brilliantly, the error was on my side. I entered a foreign character in the code.