Exclude columns in listbox on userform except first two columns & last column

[abdelfattah]

Hi

I have this part of my project when I select the sheet name from combobox1 will populate about five columns as showing in the code ,but what I want populating specific columns . just populate columns A,B and the last column for each sheet when slect from combobox1.

the last column will change in location (this means dynamic when change in location)

whn populat th last olumn should be filled value an some cells contains blank or filled the whole column , but if the whole column is empty except the header in row1 then shhould ignore it , just bring the last column contains values.

so should exclude the whole columns except A,B and last column , based on my details will populate just three columns with change in location for last column .
here is the code .

Private Sub UserForm_Initialize()
 
 Crit = ""
  Dim i As Long
  For i = 1 To Sheets.Count
   If Sheets(i).Name <> "DATA" And Sheets(i).Name <> "MAIN" Then
   ComboBox1.AddItem Sheets(i).Name
   End If
  Next
  
  If ComboBox1.ListIndex > -1 Then
    Set ws = Sheets(ComboBox1.Value)
    Call LBoxPop
    
  End If
  If ComboBox1.Value = "" Or ComboBox1.Value <> "" Then CommandButton1.Enabled = False
  
      ListBox1.SetFocus



  
End Sub



Private Sub LBoxPop()
    Dim r          As Long, c As Long
    Dim Data()     As Variant
    Dim rng        As Range
    Dim va
     
   Debug.Print ws.Name
    Set rng = ws.Cells(1, 1).CurrentRegion
'    ReDim Data(1 To rng.Rows.Count, 1 To rng.Columns.Count + 1)
    Data = ws.Cells(1, 1).CurrentRegion.Value
    
    For i = 1 To UBound(Data, 1)
        Data(i, 1) = Format(Data(i, 1), "yyyy-mm-dd")
    Next
    
    For i = 1 To UBound(Data, 1)
        Data(i, 3) = Format(Data(i, 3), "0.00")
        Data(i, 4) = Format(Data(i, 4), "0.00")
        Data(i, 5) = Format(Data(i, 5), "0.00")
    Next

    
     Me.ListBox1.List = Data
 
    With UserForm1.ListBox1
        .ColumnCount = 5
        .columnWidths = "90;300;120;120;100"
        .List = Data
    End With
 
 
End Sub

thanks

 

[NdNoviceHlp]

Not sure that I understand. Does your listbox have more than 3 columns? Dave
ps. if row 2 of your "filled" column is blank then the "LastCol" is probably going to be a problem.

 

[abdelfattah]

Not sure that I understand. Does your listbox have more than 3 columns? Dave

no , but based on the picture , yes but empty
see the so far away space if you depends on .ColumnWidths = "65;65;65"

 

[NdNoviceHlp]

You can change the column width numbers to suit. Dave

 

[NdNoviceHlp]

The listbox width has to be similar to the sum of the width of the columns. Not sure if this will work for you, but you can change the listbox width in the activation event (remove from initialize code). Dave

Private Sub UserForm_Activate()
With UserForm1.ListBox1
.ColumnCount = 3
.ColumnWidths = "65;65;65"
'add column widths plus 3 to avoid scrollbar
.Width = 65 + 65 + 65 + 3
'.IntegralHeight = False
End With
End Sub

ps. you may need to set the integral height to False ie. uncomment that line of code

 

[NoSparks]

The way I interpret the OP's request maybe something along the lines of this
(and I'm the first to admit not being any good with arrays)

Private Sub UserForm_Initialize()
    With UserForm1.ListBox1
        .ColumnCount = 3
        .ColumnWidths = "50,50,50"
    End With
    Call testing
End Sub

Sub testing()
    Dim i As Long, j As Long
    Dim lr As Long, lc As Long
    Dim arr()

With Sheets("Sheet1")
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lc = .Rows(1).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    ReDim arr(0 To lr - 1, 0 To lc - 1)
    arr = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
End With

For i = 1 To UBound(arr, 1)
    j = UBound(arr, 2)
    Do While IsEmpty(arr(i, j))
        j = j - 1
    Loop
    arr(i, 3) = arr(i, j)
Next i

Me.ListBox1.List = arr

End Sub

 

[abdelfattah]

@NdNoviceHlp
thank you so much .

 

[abdelfattah]

@NoSparks
it's excellent !
thank you so much