Display empty cells in rows on the listbox

[azizls4]

hi
i need vba code ,
I have 6 columns and many rows in multiple sheets , I want to display the rows that contains an empty cell in the 5th and 6th columns on listbox.

Meaning that the rows that contain empty cells in columns E and F in all sheets displayed on listbox

thank you

 

[DanteAmor]

I'm going to assume that the listbox is listbox1, that you have it in a userform and that you already configured it with 6 columns.
Try:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim f As Range
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim a As Variant
  
  For Each sh In Sheets
    Set f = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
    If Not f Is Nothing Then
      lr = f.Row
      a = sh.Range("A1:F" & lr).Value
      For i = 1 To UBound(a, 1)
        If a(i, 5) = "" And a(i, 6) = "" Then
          With ListBox1
            .AddItem
            For j = 1 To 6
              .List(.ListCount - 1, j - 1) = a(i, j)
            Next
          End With
        End If
      Next
    End If
  Next
End Sub

 

[azizls4]

I'm going to assume that the listbox is listbox1, that you have it in a userform and that you already configured it with 6 columns.
Try:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim f As Range
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim a As Variant
 
  For Each sh In Sheets
    Set f = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
    If Not f Is Nothing Then
      lr = f.Row
      a = sh.Range("A1:F" & lr).Value
      For i = 1 To UBound(a, 1)
        If a(i, 5) = "" And a(i, 6) = "" Then
          With ListBox1
            .AddItem
            For j = 1 To 6
              .List(.ListCount - 1, j - 1) = a(i, j)
            Next
          End With
        End If
      Next
    End If
  Next
End Sub

The code is very magnificent, thank you.

There is only a simple thing that I want to modify.

The code only displays column A in the listbox. Is it possible to display in the listbox column A and column B and the sheet that contains empty cell ?

 

[DanteAmor]

The code only displays column A in the listbox.

You must modify the properties of your listbox for 6 columns.

Is it possible to display in the listbox column A and column B and the sheet that contains empty cell ?

These are things that you must specify in your original request.

Try the following:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim f As Range
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim a As Variant
  
  With ListBox1
    .ColumnCount = 3
    
    For Each sh In Sheets
      Set f = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
      If Not f Is Nothing Then
        lr = f.Row
        a = sh.Range("A1:F" & lr).Value
        For i = 1 To UBound(a, 1)
          If a(i, 5) = "" And a(i, 6) = "" Then
            .AddItem
            .List(.ListCount - 1, 0) = a(i, 1)
            .List(.ListCount - 1, 1) = a(i, 2)
            .List(.ListCount - 1, 2) = sh.Name
          End If
        Next
      End If
    Next
    
  End With
End Sub

 

[azizls4]

You must modify the properties of your listbox for 6 columns.


These are things that you must specify in your original request.

Try the following:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim f As Range
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim a As Variant
 
  With ListBox1
    .ColumnCount = 3
   
    For Each sh In Sheets
      Set f = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
      If Not f Is Nothing Then
        lr = f.Row
        a = sh.Range("A1:F" & lr).Value
        For i = 1 To UBound(a, 1)
          If a(i, 5) = "" And a(i, 6) = "" Then
            .AddItem
            .List(.ListCount - 1, 0) = a(i, 1)
            .List(.ListCount - 1, 1) = a(i, 2)
            .List(.ListCount - 1, 2) = sh.Name
          End If
        Next
      End If
    Next
   
  End With
End Sub

thank you so much , it’s perfect
I appreciate that