Excel VBA: First execution of Advanced Filter returns header, and then the filtered results

rbsam

New Member
Joined
Jul 12, 2019
Messages
42
I have an Advanced Filter set up in my spreadsheet, and rather than showing all of the filtered results at once, I have it set up so it cycles through them one by one. But I have noticed that the first time I click the button that executes the script, it returns the header value first and then cycles through the rest. It doesn't seem to return the header again. So the only time it does it is if you close Excel and reopen the spreadsheet, then execute, it returns the header.


It is made up of 3 subs:


FilterData = Actually does the filtering of the results, this is where I expect I need to put the error message if it doesn't find a match


ShowAll = This resets the filtered results so it shows everything


GetNextResult = My spreadsheet doesn't actually show the filtered results, it replaces 2 text boxes with the values from the filter result and changes on every execution




Code:
Public Sub FilterData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")


    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row




    Dim CriteriaRange As Range
    Set CriteriaRange = ws.Range("J2", "L3")


    Dim DataRange As Range
    Set DataRange = ws.Range("A5", "I" & LastRow)




DataRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=CriteriaRange, Unique:=False






End Sub


Code:
Public Sub ShowAll()
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
End Sub




Code:
Public Sub GetNextResult()
    FilterData












    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")




    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row


    Dim DataRange As Range
    Set DataRange = ws.Range("A5", "I" & LastRow) 


    Dim FilteredData As Range
    Set FilteredData = DataRange.Resize(ColumnSize:=1).SpecialCells(xlCellTypeVisible)


    If CurrentRow + 1 > FilteredData.Cells.Count Then
        CurrentRow = 1
    End If


    CurrentRow = CurrentRow + 1
    Dim i As Long
    Dim Cell As Variant
    For Each Cell In FilteredData
        i = i + 1
        If i = CurrentRow Then
            Call ShowAll
            
            TextboxName = "Box1"
            ActiveSheet.Shapes(TextboxName).DrawingObject.Text = Cell.Offset(0, 2)
            
            TextboxName2 = "Box2"
            ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = Cell.Offset(0, 3)
            
            
            Call quick_artwork
        End If
    Next Cell




In case it's relevant:


The data headings are on A5 to I5
So the data begins on A6 to I6


The criteria selection headings are J2 to L2
and the actual selection is J3 to L3


Any ideas? Thanks!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top