How can I hide a row in listbox?

[matix003]

I have some records being displayed in a listbox. I want to be able to click on a record in the listbox and the click on a button to hide it. I do not want the row to be deleted from the excel sheet. How can I do this?

 

[RoryA]

Don't link the listbox to a range directly. Populate the control using an array, then you can remove items as you need to.

 

[matix003]

Don't link the listbox to a range directly. Populate the control using an array, then you can remove items as you need to.

Hi @RoryA ,

This is the code for my listbox.

Sub Employee_Listbox()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("EMPMaster")
    
    Dim last_row As Long
    last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
    If last_row = 1 Then last_row = 2
    
    With Me.ListBox2
        .ColumnHeads = True
        .ColumnCount = 6
        .ColumnWidths = "150,70,100,50,70,0"
        .RowSource = sh.Name & "!A2:F" & last_row
    End With
 
End Sub

What changes do I need to make? Can you please help me out the code? Do I need to use autofilter?

 

[RoryA]

Change this:

.RowSource = sh.Name & "!A2:F" & last_row

to this:

.List = sh.Range("A2:F" & last_row).Value

You can then use removeitem in your other code without having to delete the actual data on the sheet.

 

[matix003]

Change this:

.RowSource = sh.Name & "!A2:F" & last_row

to this:

.List = sh.Range("A2:F" & last_row).Value

You can then use removeitem in your other code without having to delete the actual data on the sheet.

Thank you so much! This works! I am not getting the headers anymore though. Should I create a separate listbox for the header or can it be done in the code that I have already provided?

 

[RoryA]

That is the trade-off. You can only use automatic column headers if the source data is in a range so you need to work around that in whatever way suits your setup. (could be another listbox; labels above the listbox; using the first row as headers)

 

[matix003]

That is the trade-off. You can only use automatic column headers if the source data is in a range so you need to work around that in whatever way suits your setup. (could be another listbox; labels above the listbox; using the first row as headers)

I see. I will use another listbox for this.

 

[matix003]

Change this:

.RowSource = sh.Name & "!A2:F" & last_row

to this:

.List = sh.Range("A2:F" & last_row).Value

You can then use removeitem in your other code without having to delete the actual data on the sheet.

This works but when I reload the userform the hidden rows get loaded as well into the listbox. Is there a way to stop hidden rows from getting displayed every time the userform is opened?

 

[RoryA]

You will have to loop through the range and only put the visible cells into an array. Give me a few minutes and I'll come up with some code for that.

 

[matix003]

You will have to loop through the range and only put the visible cells into an array. Give me a few minutes and I'll come up with some code for that.

Sure. It would be of great help.