Array on visible cells

[miami2k]

Dear All,

I have this code to define an array and populate a listbox>

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("TimeSheet")

Dim arrTwoD()

   Dim intRows
   Dim intCols
   Dim i As Integer
   Dim j As Integer
   
   intRows = ws.UsedRange.Rows.Count
   intCols = 8

     ReDim Preserve arrTwoD(1 To intRows, 1 To intCols)

   For i = 1 To UBound(arrTwoD, 1)
      For j = 1 To UBound(arrTwoD, 2)
 arrTwoD(i, j) = ws.Cells(i, j)
      Next

   Next

 With Scadenziario.ListBox1
  .Clear
  .ColumnCount = 8
  .ColumnWidths = "0;180;120;60;60;60;60;270;"
End With

Scadenziario.ListBox1.List = arrTwoD

    Sheets("SCFI").Select

I'd like to change it to show only visible cell so when I apply a filter to the sheet I can update the listbox on filtered data.


Thank you in advance


miami2k

 

[Fluff]

How about

   Dim arrTwoD As Variant
   Dim intRows As Long, intCols As Long, i As Long, j As Long
   Dim Cl As Range
   
   With Sheets("TimeSheet")
      intRows = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count
      intCols = 8

      ReDim arrTwoD(1 To intRows, 1 To intCols)
   
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         i = i + 1
         For j = 0 To intCols - 1
            arrTwoD(i, j + 1) = Cl.Offset(, j).Value
         Next j
      Next Cl
   End With
    With Scadenziario.ListBox1
     .Clear
     .ColumnCount = 8
     .ColumnWidths = "00;180;120;60;60;60;60;270;"
     .List = arrTwoD
   End With

 

[miami2k]

I like

How about

   Dim arrTwoD As Variant
   Dim intRows As Long, intCols As Long, i As Long, j As Long
   Dim Cl As Range
 
   With Sheets("TimeSheet")
      intRows = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count
      intCols = 8

      ReDim arrTwoD(1 To intRows, 1 To intCols)
 
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         i = i + 1
         For j = 0 To intCols - 1
            arrTwoD(i, j + 1) = Cl.Offset(, j).Value
         Next j
      Next Cl
   End With
    With Scadenziario.ListBox1
     .Clear
     .ColumnCount = 8
     .ColumnWidths = "00;180;120;60;60;60;60;270;"
     .List = arrTwoD
   End With

I like it but when I get to this line I get an overflow message!

intRows = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count

miami2fk

 

[Fluff]

Is introws declared as Long?

 

[miami2k]

yes, this is the full code, I've adapted to another routine. I might have made a mistake somewhere...

Sub PopolaTimesheet()

   Dim arrTwoD As Variant
   Dim intRows As Long, intCols As Long, i As Long, j As Long
   Dim Cl As Range
   
   Sheets("TimeSheet").Visible = True
   
   Sheets("TimeSheet").Select
   
With Sheets("TimeSheet")
      intRows = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count
      intCols = 10
      
      ReDim arrTwoD(1 To intRows, 1 To intCols)
   
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         i = i + 1
         For j = 0 To intCols - 1
            arrTwoD(i, j + 1) = Cl.Offset(, j).Value
         Next j
      Next Cl
   End With
    With Scadenziario.ListBox1
     .Clear
     .ColumnCount = 10
     .ColumnWidths = "0;60;50;200;90;110;220;45;45;45;"
     .List = arrTwoD
   End With
   
    Sheets("SCFI").Select

End Sub

 

[Fluff]

Is the code in the same workbook as the TimeSheet sheet?