VBA to filter specifc rows

[mr_hiboy]

Hi all,

I have a large list of products, each with a product total line, e.g.

Product1
Product1
Product1
Product1 Total
Product2
Product2
Product2
Product2 Total

I'd like a button to be able to filter to only see the total lines.

It need to ignore "0 Total" as i have a few blanks.

Any help would be mush appreciated.

Thanks
Paul

 

[Luke M]

Fastest way would be to record a macro of you applying a AutoFilter, and filter for these 2 criteria:
Contains: Total
Does not equal: 0 Total
Stop the recording, and look at the VB. Should be one or two lines that you need.

 

[mr_hiboy]

Hi Luke, thanks for the reply.

Yeah i though of this, but the length of the product list is inconsistent, I'd prefer something more robust in case list become longer than recorded macro.

Cheers
Paul

 

[mr_hiboy]

Actually been trying to figure it out, I'm able to show the totals, but struggling with not showing 0Total

Sub ShowTotals()
'
'
Application.ScreenUpdating = False

Range("B4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="*Total"


Range(Selection, Selection.End(xlDown)).Select

Application.ScreenUpdating = True

End Sub

Tried added second criteria

Selection.AutoFilter Field:=1, Criteria2:="<>0"

Took your advise and recorded a few and came up with this, which doesn't work.

Sub ShowTotals()
'
'
Application.ScreenUpdating = False

Range("B4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Array("*Total", "<>0Total")

Range(Selection, Selection.End(xlDown)).Select

Application.ScreenUpdating = True

End Sub

Cheers

 

[Luke M]

Hi mr_hiboy,

Thanks for recording the macro, and posting your efforts. Let's us have a nice starting point.
You were pretty close. Need to list Criteria1 and Criteria2, like so. I've also added a line to find the last row in col B, which you mentioned you wanted.

Sub FilterTotal()
Dim lastRow As Long
'Find the last row in col B
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   
    'Apply filter
    Range("B4:B" & lastRow).AutoFilter field:=1, Criteria1:="*Total", Operator:=xlAnd, Criteria2:="<>0"
End With
End Sub

 

[mr_hiboy]

Cheers Luke, thanks for you help.

i would have struggled with "and" bit!