Handling formulas as values & Ignore blanks - Using array to consolidate data

[MurdochQuill]

Hi,

As title suggests, I'm using an array for data consolidation. I keep erroring when trying to copy columns with any formulas in them... is there any way around this when using this method ?
I would also really like to ignore blanks cells & not bring them into the array.

Sub Arraycol()

Application.ScreenUpdating = False
    'Loop through worksheets, put the values in column F into arr array
Dim arr(1 To 10000), cnt As Integer, i As Integer
cnt = 0

For Each ws In Worksheets
    If ws.Name <> "Stitcher" Then
        For i = 1 To ws.Cells(rows.Count, "F").End(xlUp).Row
            cnt = cnt + 1
            arr(cnt) = ws.Cells(i, "F").Value
        Next i
    End If
Next ws

    'Loop through arr array, populate value into Stitcher sheet, column A
For i = 1 To cnt
    ThisWorkbook.Sheets("Stitcher").Cells(i, "A") = arr(i)
Next i

Application.ScreenUpdating = True
End Sub

Any help is appreciated!

 

[Fluff]

Do those formula cells contain errors such as #N/A #VALUE etc?

 

[MurdochQuill]

Do those formula cells contain errors such as #N/A #VALUE etc?

I believe it's possible. However they are IFERROR(dothing,""), so they should be blank.

 

[Fluff]

In that case what error do you get & on which line?

 

[MurdochQuill]

In that case what error do you get & on which line?

Subscript out of range, error 9.
On this line:

arr(cnt) = ws.Cells(i, "F").Value

 

[Fluff]

What is the last row in that column when you get the error, including any cells with a formula that returns ""

 

[MurdochQuill]

What is the last row in that column when you get the error, including any cells with a formula that returns ""

I think I figured it out from some trial and error investigation... It's not handling all my sheets. I can only do about 9 sheets that have just over a thousand rows each, works flawlessly.

However, still trying to find out how to exclude all blank cells from being picked up in the array.

 

[Fluff]

How about

Sub Arraycol()

Application.ScreenUpdating = False
    'Loop through worksheets, put the values in column F into arr array
Dim arr As Variant, cnt As Long, i As Long
ReDim arr(1 To Rows.Count - 1, 1 To 1)

For Each Ws In Worksheets
    If Ws.Name <> "Stitcher" Then
        For i = 1 To Ws.Cells(Rows.Count, "F").End(xlUp).Row
            If Ws.Cells(i, "F").Value <> "" Then
               cnt = cnt + 1
               arr(cnt, 1) = Ws.Cells(i, "F").Value
            End If
        Next i
    End If
Next Ws

ThisWorkbook.Sheets("Stitcher").Cells(1, "A").Resize(cnt).Value = arr

Application.ScreenUpdating = True
End Sub

 

[MurdochQuill]

How about

Sub Arraycol()

Application.ScreenUpdating = False
    'Loop through worksheets, put the values in column F into arr array
Dim arr As Variant, cnt As Long, i As Long
ReDim arr(1 To Rows.Count - 1, 1 To 1)

For Each Ws In Worksheets
    If Ws.Name <> "Stitcher" Then
        For i = 1 To Ws.Cells(Rows.Count, "F").End(xlUp).Row
            If Ws.Cells(i, "F").Value <> "" Then
               cnt = cnt + 1
               arr(cnt, 1) = Ws.Cells(i, "F").Value
            End If
        Next i
    End If
Next Ws

ThisWorkbook.Sheets("Stitcher").Cells(1, "A").Resize(cnt).Value = arr

Application.ScreenUpdating = True
End Sub

Thanks, your assistance helped me work it out on my own before I got a chance to post here.

Here was what I did :

Sub Arraycol()
Application.ScreenUpdating = False
    Dim sht As Worksheet
    Dim wb As Workbook
    Dim colSheets As Collection
    Set colSheets = New Collection
    Set tb = ThisWorkbook

    Dim arr(1 To 10000), cnt As Integer, i As Integer
            cnt = 0
              colSheets.Add Worksheets("xxx")
              colSheets.Add Worksheets("xxx")

        For Each sht In colSheets
  
               For i = 1 To sht.Cells(Rows.Count, "K").End(xlUp).Row
    [B]              If sht.Cells(i, "K") <> "" And sht.Cells(i, "K") <> "xxxxxxxx" Then[/B]
                   cnt = cnt + 1
                   arr(cnt) = sht.Cells(i, "K")
                  End If
               Next i
        Next sht

            For i = 1 To cnt
             tb.Sheets("Stitcher").Cells(i, "A") = arr(i)
            Next i

End Sub

Also, by the way. I have one more question to ask of you if you don't mind..
What would be the way to pull the array from another workbook?

I was thinking of putting a For/Next loop around the "for each sht in colsheets" loop to be similar to the following:

 For Each wb In Application.Workbooks
            If wb.Name Like "Blahblah" And wb.Name <> tb.Name Then

However I can't get it to work.

 

[Fluff]

Very difficult to tell as your code cannot work & you haven't said what you are trying to do.