Macro to delete cells with "" in a column

Question

I have a column full of either numbers or blanks, except Excel doesn't treat them as blanks because they are actually a formula like =IFERROR(A1+B1,""). I would like a macro to copy this column to a staging area, delete any cells that are "blank", and copy the trimmed-down column to a third location.

Example:

Sheet1!C2:C8 contains {1,"","",4,5,"",8}

This should be copied to Sheet2, trimmed down to contain {1,4,5,8}, and then pasted in row B of Sheet3.

Answer 1

If you add the VBA-Better-Array class to your VBA project, you can use this code to extract the values from Sheet1, perform the filter inside VBA (without needing a staging sheet) and then write the output to Sheet3

Sub FilterAndCopyRange()

    'Set source and destination
    Dim RngSource As Range
    Set RngSource = ThisWorkbook.Sheets("Sheet1").Range("C2:C8")
    
    Dim RngDestination As Range 'Note that we only pass the top-left cell of where we want the data to be written.
    Set RngDestination = ThisWorkbook.Sheets("Sheet3").Range("B1")
    
    'Create BetterArray from source range
    Dim Arr As BetterArray
    Set Arr = New BetterArray
    Arr.FromExcelRange RngSource
    
    'Filter out blank values
    Arr.Filter ""
    
    'Write the value back to the sheet
    Arr.ToExcelRange RngDestination

End Sub