Dadurch wird der Scrollbalken rechts an der Seite nutzlos.
Jemand eine Idee, wie man die leeren Zeilen automatisch löschen lassen kann?
Private Sub setList()
Dim varList() As Variant
Dim lngRow As Long, lngLast1 As Long, lngLast2 As Long, lngIndex As Long
'letzte Zeile ermitteln
lngLast1 = Cells(Rows.Count, 1).End(xlUp).Row
'Anzahl der Zeilen im Filter ermitteln
lngLast2 = Application.WorksheetFunction.Subtotal(3, ActiveSheet.Range("A2:A" & lngLast1))
ReDim varList(lngLast2 - 1, 6)
For lngRow = 2 To lngLast1
If Rows(lngRow).Hidden = False Then
varList(lngIndex, 0) = Cells(lngRow, 1)
varList(lngIndex, 1) = Cells(lngRow, 3)
varList(lngIndex, 2) = Cells(lngRow, 7)
varList(lngIndex, 3) = Cells(lngRow, 9)
varList(lngIndex, 4) = Cells(lngRow, 10)
varList(lngIndex, 5) = Cells(lngRow, 14)
varList(lngIndex, 6) = Cells(lngRow, 15)
lngIndex = lngIndex + 1
End If
Next
If lngIndex > 0 Then
setColCountAndWidth ListBox1, varList
End If
End Sub
Gruß von Luschi
Private Sub CommandButton1_Click()
setList
End Sub
Private Sub setList()
Dim varList() As Variant, varTemp() As Variant
Dim lngRow As Long, lngLast As Long, lngIndex As Long, i As Integer
lngLast = Cells(Rows.Count, 1).End(xlUp).Row
lngIndex = -1
For lngRow = 2 To lngLast
With Rows(lngRow)
If UCase(.Cells(3)) Like UCase(TextBox1) & "*" And _
UCase(.Cells(7)) Like UCase(TextBox2) & "*" And _
UCase(.Cells(1)) Like UCase(TextBox3) & "*" And _
UCase(.Cells(10)) Like UCase(TextBox4) & "*" And _
UCase(.Cells(9)) Like UCase(TextBox5) & "*" And _
UCase(.Cells(14)) Like UCase(TextBox6) & "*" And _
UCase(.Cells(15)) Like UCase(TextBox7) & "*" Then
lngIndex = lngIndex + 1
ReDim Preserve varList(0 To 6, 0 To lngIndex)
varList(0, lngIndex) = .Cells(1)
varList(1, lngIndex) = .Cells(3)
varList(2, lngIndex) = .Cells(7)
varList(3, lngIndex) = .Cells(9)
varList(4, lngIndex) = .Cells(10)
varList(5, lngIndex) = .Cells(14)
varList(6, lngIndex) = .Cells(15)
End If
End With
Next
Select Case lngIndex
Case Is > 0
varTemp = WorksheetFunction.Transpose(varList)
setColCountAndWidth ListBox1, varTemp
Case 0
ReDim vartmp(0 To 0, 0 To 6)
For i = 0 To 6
vartmp(0, i) = varList(i, 0)
Next i
setColCountAndWidth ListBox1, varTemp
Case Else
ListBox1.Clear
End Select
End Sub