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]

I didn't test this, but it should work. You will have to add in what you want to do with the single row.

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
    If Application.CountA(rng.Offset(1).SpecialCells(xlCellTypeVisible)) > 0 Then 'Check if criteria found.
        [COLOR=#0000ff]If SeachItem = "ABC" Then
[/COLOR]          Set rng = rng.SpecialCells(xlCellTypeVisible).Rows(1)
            .List = rng.Value
            [COLOR=#ff0000]'What happens to the single row?
[/COLOR]      [COLOR=#0000ff]Else
[/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 If
    End With
    If rng.Parent.FilterMode Then
        rng.Parent.ShowAllData
    End If
    'rng.Parent.ShowAllData
End Sub

Just a tip on solving some of the probblems with making mods to code, and designing code for that matter: Draw a block diagram of the steps you want to take to build the code. Something like a decision chart that shows the logical path that must be followed to get from point A to point Z. When doing a mod, draw the existing steps and then see where the mod needs to go in that structure to make it flow through.

 

[kelly mort]

Done and problem solved.

I appreciate you note you added to the post

I will do that in the future