Populate to Listbox depending of range and column

[Gemitec]

Hello

My question is: Is there a possibility to populate Data in a Listbox, where X Rows and Y columns are met, but only show the columns where columns are not empty?

As you see on the picture, your see there are some rows without data in columns.
So my Aim is to populate the rows and DELETE the columns, if columns have no data

I uploaded a picture, there you see, I dont need Columne J:P , R:S, U:X as an example, but these can difference

(The basic program is set up, I just need to programatically delete all those columnes in a range, what have no data (are empty))

Thank you

 

[DanteAmor]

Is there a possibility to populate Data in a Listbox, where X Rows and Y columns are met, but only show the columns where columns are not empty?

How about:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long
  Dim a As Variant
  Dim xWidth As String
  Dim xData As Boolean
  
  With Sheets("Sheet1")
    a = .Range("C6:AB" & .Range("C" & Rows.Count).End(3).Row).Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    For j = 1 To UBound(a, 2)
      xData = False
      For i = 2 To UBound(a, 1)
        If a(i, j) <> "" Then xData = True: Exit For
      Next
      xWidth = IIf(xData, xWidth & Int(.Cells(6, j + 2).Width + 1) & " pt;", xWidth & "0 pt;")
    Next
  End With
  With ListBox1
    .ColumnCount = UBound(a, 2)
    .ColumnWidths = xWidth
    .List = a
  End With
End Sub

 

[Gemitec]

Yes thank you very much

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Gemitec]

Really helped me a lot, but Your code doesnt work if the first Cell is empty.

So I made some changes, but now I struggeling that the column widths in the Listbox9 (in this example) are not equal to the size of the worksheet cells width. Doesnt matter actually, I can live with it
The range is from A6:AB, but the data that has to be listed are from I6:AB. Other listboxes (Listbox1-Listbox8) fills the sheet with data, and then your code comes in. (as you see on the picture, each row has different values, some columnes are empty and I didnt want those to bee shown, it works perfectly. I just placed artcleNr into Columne I, so no cells are empty there and the code works like this)

Dim rngData As Range
Dim i As Long, j As Long
Dim a As Variant
Dim xWidth As String
Dim xData As Boolean

Sheets("Article Calculation").Select

Set rngData = ArticleCriteria.Range("A6").CurrentRegion

ListBox9.ColumnHeads = True
ListBox9.ColumnCount = rngData.Columns.Count - 8

Set rngData = rngData.Resize(rngData.Rows.Count - 1).Offset(1, rngData.Columns.Count - ListBox9.ColumnCount)

With Sheets("Article Calculation")
a = .Range("i6:AB" & .Range("i" & Rows.Count).End(3).Row).Value

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For j = 1 To UBound(a, 2)
xData = False
For i = 2 To UBound(a, 1)
If a(i, j) <> "" Then xData = True: Exit For
Next
xWidth = IIf(xData, xWidth & Int(.Cells(6, j + 0).Width + 0) & " pt;", xWidth & "0 pt;")
Next
End With

ListBox9.RowSource = "'" & rngData.Parent.Name & "'!" & rngData.Address

With ArtikelSuche.ListBox9
.ColumnWidths = xWidth
End With

End Sub

 

[DanteAmor]

Your code doesnt work if the first Cell is empty.

Which cell?

You can put real examples and show here the example when you have the problem.
Saying "Your code doesn't work if the first Cell is empty" doesn't help enough.
I am happy to make the necessary changes but help me with some examples.

Use code tag to put code.
Use XL2BB tool to put minisheets.

 

[Gemitec]

Hello, sure I show you, but as I said I fixed it.
This code is yours, I just made other range (from I:AB)
So if "I" is empty, Listbox9 return no data. Please see attachments.

If I use this code from you

Private Sub Listbox9fill()

Dim i As Long, j As Long
Dim a As Variant
Dim xWidth As String
Dim xData As Boolean

With Sheets("Article Calculation")
a = .Range("i6:AB" & .Range("i" & Rows.Count - 1).End(3).Row).Value

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For j = 1 To UBound(a, 2)
xData = False
For i = 2 To UBound(a, 1)
If a(i, j) <> "" Then xData = True: Exit For
Next
xWidth = IIf(xData, xWidth & Int(.Cells(6, j + 2).Width + 1) & " pt;", xWidth & "0 pt;")
Next
End With
With ArtikelSuche.ListBox9
ListBox9.ColumnHeads = True

.ColumnCount = UBound(a, 2)
.ColumnWidths = xWidth
.List = a
End With

End Sub


Then The error happens here, because if the first checked cell empty, your code hides everything after.

The Data comes from a sheet where all other listboxes filter out but that doesnt have to do nothing.

My code also put in some Header into listbox, since I also need those info, see A4.png

But your code helped a lot, thanks Sir

 

[Gemitec]

I could upload a test file for you , but I dont know how to do that

 

[DanteAmor]

So if "I" is empty, Listbox9 return no data. Please see attachments.

Generally, a column is taken as the base, assuming that column will have data, but if it has no data or there is no "base" column then we have to read all the cells.
Change this:

With Sheets("Article Calculation")
a = .Range("i6:AB" & .Range("i" & Rows.Count - 1).End(3).Row).Value

For this:

  With Sheets("Article Calculation")
    lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    a = .Range("I6:AB" & lr).Value

_____________________

Let me know if you have another detail and we will gladly review it, the idea is to finish your project.

 

[Gemitec]

Perfect, thank you very much again
I will stick to the first methode, where first column has to be filled, since I find that more stable version. And besides, I like it in listbox as first column to have the articleNumbers