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]

OK, add this function to your module:

Function VisibleCellsAsArray(inputRange As Range)
   Dim dataIn
   dataIn = inputRange.Value
   
   Dim outputArray()
   Dim outputRowCount As Long
   On Error Resume Next
   outputRowCount = inputRange.Columns(1).SpecialCells(xlCellTypeVisible).Cells.CountLarge
   On Error GoTo 0
   
   If outputRowCount > 0 Then
      
      If Not IsArray(dataIn) Then
      
         ReDim outputArray(1 To 1, 1 To 1)
         outputArray(1, 1) = inputRange.Value
      Else
      
         ReDim outputArray(1 To outputRowCount, 1 To UBound(dataIn, 2))
         Dim rowNum As Long, colNum As Long, outIndex As Long
         outIndex = 1
         For rowNum = LBound(dataIn) To UBound(dataIn)
            If Not inputRange.Rows(rowNum).EntireRow.Hidden Then
               For colNum = LBound(dataIn, 2) To UBound(dataIn, 2)
                  outputArray(outIndex, colNum) = dataIn(rowNum, colNum)
               Next colNum
               outIndex = outIndex + 1
            End If
         Next rowNum
         
      End If
   End If
   VisibleCellsAsArray = outputArray
End Function

then when you need to load the listbox, use:

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

 

[matix003]

OK, add this function to your module:

Function VisibleCellsAsArray(inputRange As Range)
   Dim dataIn
   dataIn = inputRange.Value
  
   Dim outputArray()
   Dim outputRowCount As Long
   On Error Resume Next
   outputRowCount = inputRange.Columns(1).SpecialCells(xlCellTypeVisible).Cells.CountLarge
   On Error GoTo 0
  
   If outputRowCount > 0 Then
     
      If Not IsArray(dataIn) Then
     
         ReDim outputArray(1 To 1, 1 To 1)
         outputArray(1, 1) = inputRange.Value
      Else
     
         ReDim outputArray(1 To outputRowCount, 1 To UBound(dataIn, 2))
         Dim rowNum As Long, colNum As Long, outIndex As Long
         outIndex = 1
         For rowNum = LBound(dataIn) To UBound(dataIn)
            If Not inputRange.Rows(rowNum).EntireRow.Hidden Then
               For colNum = LBound(dataIn, 2) To UBound(dataIn, 2)
                  outputArray(outIndex, colNum) = dataIn(rowNum, colNum)
               Next colNum
               outIndex = outIndex + 1
            End If
         Next rowNum
        
      End If
   End If
   VisibleCellsAsArray = outputArray
End Function

then when you need to load the listbox, use:

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

Hi @RoryA ! Thank you for replying. I have added the above code. But when I am reopening the userform the removed row is still getting loaded.
Here is the code for the hide button-

Private Sub CommandButton15_Click()

If Me.ListBox2.ListIndex >= 0 Then
    Me.ListBox2.RemoveItem Me.ListBox2.ListIndex
    
End If

End Sub

 

[RoryA]

That doesn’t hide the row in the sheet, it just removes it from the current list. You’ll need to hide the row on the sheet as well

 

[matix003]

That doesn’t hide the row in the sheet, it just removes it from the current list. You’ll need to hide the row on the sheet as well

Do I need to do that manually? or can I do it in this sub itself?
I tried
If Me.ListBox2.ListIndex >= 0 Then
Me.ListBox2.ListIndex.EntireRow.Hidden = True

End If
It gives me error.

 

[RoryA]

You can do it in the sub but if the listbox is only loaded with visible rows, you cannot rely on the index in the list to be related to the row number. I'd suggest amending the workbook to use an additional column in the listbox with the row number in the new column - it can be hidden by amending the relevant part of the columnwidths string to 0? Alternatively, if one column is a unique key for each row, you could use Find or Match to get the row at the time you remove the item from the list.

 

[matix003]

You can do it in the sub but if the listbox is only loaded with visible rows, you cannot rely on the index in the list to be related to the row number. I'd suggest amending the workbook to use an additional column in the listbox with the row number in the new column - it can be hidden by amending the relevant part of the columnwidths string to 0? Alternatively, if one column is a unique key for each row, you could use Find or Match to get the row at the time you remove the item from the list.

Hi @RoryA ! Thank for your suggestion. Instead of hiding I have decided to delete the selected row and paste it on another sheet. I am storing the selected values right before deleting it. This works.