Listbox to display data from columns based on if cells in specified column contain values

CODE ANSWER (thanks to karma)

Private Sub UserForm_Initialize()
Call PopLB
End Sub

Sub PopLB()

With Sheets("helper")
    .Cells.Clear
    Master.UsedRange.Copy Destination:=.Range("B1")
    addr = .UsedRange.Columns(1).Offset(0, -1).Address
    .Range("A1").Value = Split(addr, ":")(0)
    .Range("A1").AutoFill Destination:=.Range(addr), Type:=xlFillSeries
    .Range(addr).Offset(0, 1).SpecialCells(xlBlanks).EntireRow.Delete '0, 1 is Request ID; 0, 2 is CTC File Number; 0, 3 is Work Order
End With

With listboxRequestsETR
    .ColumnCount = 27
    .ColumnWidths = "00,28,00,00,28,28,208,28,28,28," & _
                    "28,28,28,28,28,28,28,28,28,28," & _
                    "28,28,28,28,28,28,28"
    LastRow = Range("A" & Rows.Count).End(xlUp).row
    .RowSource = "helper!A2:AA" & LastRow
    .ColumnHeads = True
End With

End Sub

I am hoping to create a UserForm that pulls data from certain columns in a main sheet Master (Sheet1) based on if there is any data within the specified column.

Screenshot

Ideally, the ListBox listboxRequestsETR would check if there is a Request ID available Column A, and display the data in the yellow and blue columns. In this case, Row 1 would be shown as the column heads for the ListBox, and the only row that would not transfer over to the ListBox would be Row 4.

On a separate note, I am hoping to apply the same logic to separate ListBoxes with Columns B and C, such that the condition of populating the ListBox would be based on whether there is any data in the specified cell (regardless of whether the data is General or Number).

Any help would be appreciated!

I am able to populate the ListBox with data, however when I attempt to filter the data I come up with errors. Below is the code I have so far that brings up the ListBox with all the data.

NEW CODE

Private Sub UserForm_Initialize()
    Call AddDataToListBoxETR
End Sub

Private Sub AddDataToListBoxETR()
    
    '    Dim rg As Range
    '    Set rg = GetRangeETR
    
    '    With listboxRequestsETR
    
    '        .RowSource = rg.Address(external:=True)
    '        .ColumnCount = rg.Columns.Count
    '        .ColumnWidths = "75;90;100;110;75;125;125;100;100;100;100;100"
    '        .ColumnHeads = True
    '        .ListIndex = 0
    
    '    End With
   
    Dim i As Long
    For i = 2 To Master.Range("A100000").End(xlUp).Offset(1, 0).row
    If Master.Cells(i, "A").Value <> 0 Then
    Me.listboxRequestsETR.AddItem Master.Cells(i, 1).Value
    Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 2) = Master.Cells(i, "D").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 3) = Master.Cells(i, "E").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 4) = Master.Cells(i, "F").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 5) = Master.Cells(i, "G").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 6) = Master.Cells(i, "H").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 7) = Master.Cells(i, "I").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 8) = Master.Cells(i, "J").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 9) = Master.Cells(i, "K").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 10) = Master.Cells(i, "L").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 11) = Master.Cells(i, "M").Value
    'Me.listboxRequestsETR.List(listboxRequestsETR.ListCount - 1, 12) = Master.Cells(i, "O").Value
   End If
   Next i
   
End Sub

DESIRED OUTCOME

I recognize there is a column limit of 10, so the ability to display columns in the ListBox filtered by the condition (if Request ID is available in Row i) is what I am looking for

UserForm

OLD CODE

Private Sub UserForm_Initialize()
    
    Call AddDataToListBoxETR
End Sub

Private Sub AddDataToListBoxETR()
    
    Dim rg As Range
    Set rg = GetRangeETR
    
    With listboxRequestsETR
    
        .RowSource = rg.Address(external:=True)
        .ColumnCount = rg.Columns.Count
        .ColumnWidths = "75;90;100;110;75;125;125;100;100;100;100;100"
        .ColumnHeads = True
        .ListIndex = 0
    
    End With
   
End Sub

Module

Option Explicit

Public Function GetRangeETR() As Range

    Set GetRangeETR = Master.Range("A1").CurrentRegion
    Set GetRangeETR = GetRangeETR.Offset(1).Resize(GetRangeETR.Rows.Count - 1)
    
End Function

Solution

I'm still not sure if I understand you correctly. Anyway, below is just my guess about what you want .....

The LB (ListBox) in the userform will show only the row with value in column A, hide column B and C, then show column D to Z. So, in the LB, there is H01 and then H04 to H26 while the row is coming from row 2,3,5 and 6. The LB doesn't show row 4 and 7 because in column A those rows are blank/no-value.

In the Userform there are 5 textbox to update/edit the row(N) of data for H04,H05,H06,H11 and H12.

Example:
The user click one item in the LB.
Then the textbox (tb) 1 to 5 show the corresponding column value which is clicked.
Then the user update/change the value in each tb, then click UPDATE button. The DATA is updated and the LB also updated.

Private Sub UserForm_Initialize()
Call PopLB
End Sub

Sub PopLB()

With Sheets("helper")
    .Cells.Clear
    Sheets("DATA").UsedRange.Copy Destination:=.Range("B1")
    addr = .UsedRange.Columns(1).Offset(0, -1).Address
    .Range("A1").Value = Split(addr, ":")(0)
    .Range("A1").AutoFill Destination:=.Range(addr), Type:=xlFillSeries
    .Range(addr).Offset(0, 1).SpecialCells(xlBlanks).EntireRow.Delete
End With

With LB
    .ColumnCount = 27
    .ColumnWidths = "00,28,00,00,28,28,28,28,28,28," & _
                    "28,28,28,28,28,28,28,28,28,28," & _
                    "28,28,28,28,28,28,28"
    .RowSource = "helper!" & Sheets("helper").UsedRange.Address
End With

End Sub

Private Sub LB_Click()
tb1.Value = LB.List(LB.ListIndex, 4)
tb2.Value = LB.List(LB.ListIndex, 5)
tb3.Value = LB.List(LB.ListIndex, 6)
tb4.Value = LB.List(LB.ListIndex, 11)
tb5.Value = LB.List(LB.ListIndex, 12)
End Sub

Private Sub bt_Click()
If LB.ListIndex = -1 Then Exit Sub
With Sheets("DATA")
r = Range(LB.List(LB.ListIndex, 0)).Row
.Cells(r, 4).Value = tb1.Value
.Cells(r, 5).Value = tb2.Value
.Cells(r, 6).Value = tb3.Value
.Cells(r, 11).Value = tb4.Value
.Cells(r, 12).Value = tb5.Value
End With
Call PopLB
End Sub

In PopLB sub, first it clear the whole cells in sheet "helper".
Then it copy the data in sheet "DATA" to sheet "helper" cell B1.

Within sheet "helper":
it get the address of the usedrange as addr variable, then put the first split value of addr in cell A1, fill series the range of addr, then finally it delete the blank row of H01

within the LB:
It make 27 columns and set each column width. Please note that there are three zero value for the column width. One is to hide the id/row in column A, the other two is to hide H02 and H03. Finally it use the sheet helper used range as the row source for the LB.

The sub LB_Click will be triggered when the user click any item in the LB. It will populate textbox (tb) 1 to 5.

The bt_Click sub will be triggered when the user click the UPDATE button. It will update the corresponding value in the sheet DATA from the tb1 to tb5 value in the userform, then it call back the PopLB sub.

so, as you said :

this UF is meant to connect with an additional UF that can edit / delete data in selected rows.

Although maybe it's not exactly what you mean, but this UF still can update/edit the data in sheet DATA although it use a helper sheet.