I tried to display the data by filtering cells(i,17)>0, the data was filtered successfully but the display in the listview is not as expected. there is an empty row in the list view.
please enlighten me, thanks in advance.
this my code:
Private Sub CommandButton1_Click()
Set ws = Sheets("DfPinj")
On Error Resume Next
ws.Range("B1").Select
Dim ch As ColumnHeader
Dim lngRow As Long
Dim ListItem As MSComctlLib.ListItem
Dim ItemList As ListItem
Dim n As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
SetCommonListViewProperties LstData
With LstData.ColumnHeaders
Set ch = .Add(, , ws.Cells(1, 2), 75, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 3), 70, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 4), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 5), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 6), 95, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 7), 80, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 8), 100, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 9), 100, lvwColumnLeft)
Set ch = .Add(, , "JK WKT", 50, lvwColumnCenter)
Set ch = .Add(, , "JENIS PINJ", 70, lvwColumnLeft)
Set ch = .Add(, , "BGA", 30, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 13), 70, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 14), 70, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 15), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 16), 80, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 17), 70, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 18), 80, lvwColumnRight)
Set ch = .Add(, , "Late Pym", 50, lvwColumnRight)
End With
Dim rngData As Range
Dim RowCount As Long
Dim colCount As Long
Dim LstItem
Dim j As Long
Set rngData = ws.Range("B2").CurrentRegion
RowCount = rngData.Rows.Count
colCount = rngData.Columns.Count
ws.Activate
Set ItemList = Me.LstData.ListItems.Add
With LstData
.ListItems.Clear
For i = 2 To RowCount
Set ListItem = .ListItems.Add(Text:=rngData(i, 1).Value)
For j = 2 To colCount
If ws.Cells(i, 17) > 0 Then
If j = 16 Then
ListItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "#,##0")
ElseIf j = 15 Or j = 17 Then
ListItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "dd-mmm-yy")
Else
ListItem.ListSubItems.Add Text:=rngData(i, j).Value
End If
End If
Next j
Next i
End With
End Sub
Please correct my code
please enlighten me, thanks in advance.
this my code:
Private Sub CommandButton1_Click()
Set ws = Sheets("DfPinj")
On Error Resume Next
ws.Range("B1").Select
Dim ch As ColumnHeader
Dim lngRow As Long
Dim ListItem As MSComctlLib.ListItem
Dim ItemList As ListItem
Dim n As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
SetCommonListViewProperties LstData
With LstData.ColumnHeaders
Set ch = .Add(, , ws.Cells(1, 2), 75, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 3), 70, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 4), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 5), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 6), 95, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 7), 80, lvwColumnCenter)
Set ch = .Add(, , ws.Cells(1, 8), 100, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 9), 100, lvwColumnLeft)
Set ch = .Add(, , "JK WKT", 50, lvwColumnCenter)
Set ch = .Add(, , "JENIS PINJ", 70, lvwColumnLeft)
Set ch = .Add(, , "BGA", 30, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 13), 70, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 14), 70, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 15), 150, lvwColumnLeft)
Set ch = .Add(, , ws.Cells(1, 16), 80, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 17), 70, lvwColumnRight)
Set ch = .Add(, , ws.Cells(1, 18), 80, lvwColumnRight)
Set ch = .Add(, , "Late Pym", 50, lvwColumnRight)
End With
Dim rngData As Range
Dim RowCount As Long
Dim colCount As Long
Dim LstItem
Dim j As Long
Set rngData = ws.Range("B2").CurrentRegion
RowCount = rngData.Rows.Count
colCount = rngData.Columns.Count
ws.Activate
Set ItemList = Me.LstData.ListItems.Add
With LstData
.ListItems.Clear
For i = 2 To RowCount
Set ListItem = .ListItems.Add(Text:=rngData(i, 1).Value)
For j = 2 To colCount
If ws.Cells(i, 17) > 0 Then
If j = 16 Then
ListItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "#,##0")
ElseIf j = 15 Or j = 17 Then
ListItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "dd-mmm-yy")
Else
ListItem.ListSubItems.Add Text:=rngData(i, j).Value
End If
End If
Next j
Next i
End With
End Sub
Please correct my code