Filtering Data Code Adjustment Needed - VBA

[kelly mort]

I have this code here that I want to use to filter data and upload result to the listbox on a userform. So I have managed to add some pieces together and came closer, but I am left with how to trap certain errors.


The data starts on row 7. Row 6 is header – I don’t want to load header into the listbox.


I am having issues understanding what this line is doing actually.

Set rng = rng.Offset(5).Resize(rng.Rows.Count - 6, .ColumnCount)

When I change its values, 5 and 6, I see it decide which rows to start the load from. But since I have not been able to fully achieve what I want, I want to post it here so those with the capacity can have a look at it for me.


It also loads some empty rows to the listbox. Is it that of the “usedRange” causing that? If yes then I want to set up my own range like “.Range(“B7:X”&lr)”, where the lr is the last row. Is that a good idea?


Column H is where to look for the criteria from.



And since I am accepting the search criteria from an inputbox, it is possible I might not hit the right keyword at certain times. When this happen, I need to flag an alert. I am now adjusting to the filtering system and can’t seem to figure out where to pluck to make it happen – that’s when the criteria does not match anything in the field. Thanks for your time. have a nice moment.

Sub FilterData()
    Dim rCrit As Range, rng As Range, r%, c%, LBox As Object
    Set rng = Sheets(“Sheet1”).UsedRange
    Set LBox = UserForm1.ListBox1
    With Sheets(“Sheet1”)
        .[B7].CurrentRegion.AutoFilter field:=7, Criteria1:=SearchItem
    End With
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
    Set rng = rng.Offset(5).Resize(rng.Rows.Count - 6, .ColumnCount)
    Set rng = rng.SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

 

[JLGWhiz]

Set rng = rng.Offset(5).Resize(rng.Rows.Count - 6, .ColumnCount)

rng is originally set to equal the UsedRange. The Offset(5) moves the start of the rng parameters down five rows and the Resize is then based on the top left cell address of the new rng address. eg. If the original UsedRange address is A1:Z50 then the Offet 5 will move the new rng address to A6:Z55. The Resize(rng.Rows.Count -6, .Columns.Count) would then use Range("A6") as the anchor cell to change the rng address to the number of rows in the orignal UsedRange less 6, and the number of columns in the LBox.Object (listbox).
It looks like it was tailored to a specific requirement when originally written. You can change the rng address parameters to meet your needs by using the method you describe with the last row variable.

 

[kelly mort]

So I did the adjustment like this and it's working cool without adding the blank rows.

Sub FilterData()
    Dim rCrit As Range, rng As Range, r%, c%, LBox As Object
    Set rng = Sheets(“Sheet1”).Range("B7:X" & lr)
    Set LBox = UserForm1.ListBox1
    With Sheets(“Sheet1”)
        .[B7].CurrentRegion.AutoFilter field:=7, Criteria1:=SearchItem
    End With
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
    
    Set rng = rng.SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

What is left now is that when the search criterion didn't exist like I pointed out before, it raises an error while on this line:

Set rng = rng.SpecialCells(xlCellTypeVisible)

Is it a good practice to use an error handler to flag an alert when now cells are found for the filter or there is a better way to that?

 

[JLGWhiz]

It depends on what causes the error. In some cases, if you expect it to error for a certain condition, but that condition will not interfere with what you want to accomplish, you can use the 'On Error Resume Next' to ignore the error, but when you do that be sure to reset the error trigger after the pertinent code lines have executed. You do that with the 'On Error GoTo 0' and 'Err.Clear' statements. For example, if the 'SpecialCells' method is thowing the error because the filter is not producing any data in the specified range, then you might want to just ignore the error rather than use a handler. Handlers are used when you anticipate an error during run time that cannot be ignored without interrupting the code and causing grief. If you do not want to see Alerts (not errors) that you know will pop up, just turn them off with the 'Application.DisplayAlerts = False' sttatement. Again, be sure to turn them back on after the applicable code executes.

Also, if you need to skip over code lines because of an error, the a handler is a good idea.

 

[JLGWhiz]

I recall somewhere a few years back that one of the then VBA Gurus was discussing error handling and they pointed out that if you know certain conditions will cause errors, then why not write your code to avoid the condition. Then went on to point out that you can use If...Then statements to test for conditions before trying to execute code that would throw an error. That was something I picked up on and have done my best to try and incorporate into my code writing. I am not always successful, but it helped me to develop a pretty tight code writing style.

 

[kelly mort]

Yeah, so far it's only the 'SpecialCells' method that is likely to throw the error.

All others have been checked and trapped already.

I will like to use the if statements to trap it in case the filter returns nothing.

That's where I don't know how to place that in the latest script I posted.

I don't know which argument to raise for that if statement.

If ........... Then
Set 'SpecialCells' method here if cells with data found
Else
Alert here if no cells found
End If

 

[JLGWhiz]

Siff it this mod will work.

Sub FilterData()
    Dim rCrit As Range, rng As Range, r%, c%, LBox As Object
    Set rng = Sheets(“Sheet1”).Range("B7:X" & lr)
    Set LBox = UserForm1.ListBox1
    With Sheets(“Sheet1”)
        rng.AutoFilter field:=7, Criteria1:=SearchItem 'rng starts at B7
    End With
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
   [COLOR=#0000ff] If Application.CountA(rng.Offset(1).SpecialCells(xlCellTypeVisible)) > 0 Then 'Check if criteria found.
[/COLOR]        Set rng = rng.SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
   [COLOR=#0000ff] End If
[/COLOR]    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

 

[kelly mort]

Siff it this mod will work.

Sub FilterData()
    Dim rCrit As Range, rng As Range, r%, c%, LBox As Object
    Set rng = Sheets(“Sheet1”).Range("B7:X" & lr)
    Set LBox = UserForm1.ListBox1
    With Sheets(“Sheet1”)
        rng.AutoFilter field:=7, Criteria1:=SearchItem 'rng starts at B7
    End With
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
   [COLOR=#0000ff] If Application.CountA(rng.Offset(1).SpecialCells(xlCellTypeVisible)) > 0 Then 'Check if criteria found.
[/COLOR]        Set rng = rng.SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
   [COLOR=#0000ff] End If
[/COLOR]    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

Right on point!!!!

I knew you could figure it out!!!

Very grateful

 

[JLGWhiz]

Right on point!!!!

I knew you could figure it out!!!

Very grateful

Happy to help,
Regards, JLG

 

[kelly mort]

Siff it this mod will work.

Sub FilterData()
    Dim rCrit As Range, rng As Range, r%, c%, LBox As Object
    Set rng = Sheets(“Sheet1”).Range("B7:X" & lr)
    Set LBox = UserForm1.ListBox1
    With Sheets(“Sheet1”)
        rng.AutoFilter field:=7, Criteria1:=SearchItem 'rng starts at B7
    End With
    With LBox.Object
    While .ListCount > 0
        .RemoveItem 0
    Wend
   [COLOR=#0000ff] If Application.CountA(rng.Offset(1).SpecialCells(xlCellTypeVisible)) > 0 Then 'Check if criteria found.
[/COLOR]        Set rng = rng.SpecialCells(xlCellTypeVisible)
        .List = rng.Value
            For r = 0 To .ListCount - 1
                For c = 0 To .ColumnCount - 1
                    .List(r, c) = Trim(rng.Cells(r + 1, c + 1).Text)
                Next c
            Next r
   [COLOR=#0000ff] End If
[/COLOR]    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

Hi,

I just got into another pit and want a way out.

For example, if the keyword is say "ABC", then I want to load only the first row of the filtered items.

How do I do that?

So if SearchItem ="ABC" Then

Show only first filtered items in list box

Else

Show all filtered items as before

End if

Something like this