abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,503
- Office Version
- 2019
- 2010
- Platform
- Windows
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 .
thanks
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 .
VBA 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
