Remove rows from a 2d array if value in column is empty

Question

I have a large table of lab measurement logs, which I work with using arrays. (Im a chemist, a lab technician and Ive started to learn VBA only last week, please bear with me.) Im trying to figure out, how to load the table into an array and then remove rows with an empty value in the 5th column so that I can "export" the table without blanks in the 5th column via an array into a different sheet.

I first tested this with some code I found for a 1D array, where I would make 2 arrays, one placeholder array which Id loop through adding only non-blanks to a second array.

For Counter = LBound(TestArr) To UBound(TestArr)
    If TestArr(Counter, 1) <> "" Then
        NoBlankSize = NoBlankSize + 1
        NoBlanksArr(UBound(NoBlanksArr)) = TestArr(Counter, 1)
        ReDim Preserve NoBlanksArr(0 To UBound(NoBlanksArr) + 1)
    End If
Next Counter

It works in 1D, but I cant seem to get it two work with 2 dimensions.

Heres the array Im using for reading and outputting the data

Sub ArrayTest()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim TestArray() As Variant
    Dim Dimension1 As Long, Dimension2 As Long

    Sheets("Tracker").Activate

    Dimension1 = Range("A3", Range("A2").End(xlDown)).Cells.Count - 1
    Dimension2 = Range("A2", Range("A2").End(xlToRight)).Cells.Count - 1

    ReDim TestArray(0 To Dimension1, 0 To Dimension2)

    'load into array
    For Dimension1 = LBound(TestArray, 1) To UBound(TestArray, 1)
        For Dimension2 = LBound(TestArray, 2) To UBound(TestArray, 2)
            TestArray(Dimension1, Dimension2) = Range("A4").Offset(Dimension1, Dimension2).Value
        Next Dimension2
    Next Dimension1

    Sheets("Output").Activate
    ActiveSheet.Range("A2").Select

    'read from array
    For Dimension1 = LBound(TestArray, 1) To UBound(TestArray, 1)
        For Dimension2 = LBound(TestArray, 2) To UBound(TestArray, 2)
             ActiveCell.Offset(Dimension1, Dimension2).Value = TestArray(Dimension1, Dimension2)
        Next Dimension2
    Next Dimension1

    Erase TestArray

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Thank you for any help in advance.

the Redim Preserve statement does not work for two-dimensional arrays if you want to change the number of records (rows). I'd just loop through the array when filling the range with it, and skip the blank records — Tim Stack
– Tim Stack, Commented Jul 29, 2019 at 11:49
I'd do as Tim suggests. Put an If statement between your both For statements. If you care about trailing empty rows on your array, you could firstly calculate the amount of non-empty cells in the fifth column. — Luuklag
– Luuklag, Commented Jul 29, 2019 at 11:52

Tim Stack · Accepted Answer · 2019-07-29 12:08:32Z

1

The Redim Preserve statement does not work for two-dimensional arrays if you want to change the number of records (rows).

You could load the range into an array, and then when you want to export the array to another range, loop through that array while skipping blank records.

An example:

Option Explicit

Sub ArrayTest()
Dim wb As Workbook, wsInput As Worksheet, wsOutput As Worksheet
Dim myArr As Variant
Dim i As Long, k As Long, LRow As Long

Set wb = ThisWorkbook
Set wsInput = wb.Sheets("Tracker")
Set wsOutput = wb.Sheets("Output")

LRow = wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Row + 1

'Load a range into the array (example range)
myArr = wsInput.Range("A1:Z100")

'Fill another range with the array
For i = LBound(myArr) To UBound(myArr)
    'Check if the first field of the current record is empty
    If Not Len(myArr(i, 1)) = 0 Then
        'Loop through the record and fill the row
        For k = LBound(myArr, 2) To UBound(myArr, 2)
            wsOutput.Cells(LRow, k) = myArr(i, k)
        Next k
        LRow = LRow + 1
    End If
Next i

End Sub

edited Jul 29, 2019 at 12:08

answered Jul 29, 2019 at 11:58

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Flippin Pippin Over a year ago

Ah, I think I see what you mean. Thank you very much for pointing me in the right direction.

Tim Stack Over a year ago

Glad I could be of help

Ron Rosenfeld · Accepted Answer · 2019-07-29 12:39:48Z

0

From your code, it appears you want to

test a column of data on a worksheet to see if there are blanks.
if there are blanks in the particular column, exclude that row
copy the data with the excluded rows to a new area

You can probably do that easier (and quicker) with a filter: code below checking for blanks in column2

Option Explicit
Sub removeCol2BlankRows()

    Dim wsSrc As Worksheet, wsRes As Worksheet
    Dim rSrc As Range, rRes As Range

Set wsSrc = ThisWorkbook.Worksheets("sheet1")
  Set rSrc = wsSrc.Cells(1, 1).CurrentRegion 'many ways to do this

Set wsRes = ThisWorkbook.Worksheets("sheet1")
    Set rRes = wsRes.Cells(1, 10)

If wsSrc.AutoFilterMode = True Then wsSrc.AutoFilterMode = False

rSrc.AutoFilter field:=2, Criteria1:="<>"
rSrc.SpecialCells(xlCellTypeVisible).Copy rRes

wsRes.AutoFilterMode = False

End Sub

If you really just want to filter the VBA arrays in code, I'd store the non-blank rows in a dictionary, and then write it back to the new array:

Option Explicit
Sub removeCol2BlankRows()
    Dim testArr As Variant
    Dim noBlanksArr As Variant
    Dim myDict As Object
    Dim I As Long, J As Long, V
    Dim rwData(1 To 4) As Variant

With ThisWorkbook.Worksheets("sheet1")
    testArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=4)
End With

Set myDict = CreateObject("Scripting.Dictionary")

For I = 1 To UBound(testArr, 1)
    If testArr(I, 2) <> "" Then
        For J = 1 To UBound(testArr, 2)
            rwData(J) = testArr(I, J)
        Next J
        myDict.Add Key:=I, Item:=rwData
    End If
Next I

ReDim noBlanksArr(1 To myDict.Count, 1 To 4)
I = 0
For Each V In myDict.keys
    I = I + 1
    For J = 1 To 4
        noBlanksArr(I, J) = myDict(V)(J)
    Next J
Next V
End Sub

edited Jul 29, 2019 at 12:39

answered Jul 29, 2019 at 12:24

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

2 Comments

Flippin Pippin Over a year ago

Thanks for the suggestion with the filters, but Id prefer to use arrays to have it linked to a button to have the list on a different spreadsheet more conveniently updateable and so that I can work with the data easier. Edit: Sorry I didnt see your VBA code earlier, thank you very much, Ill give it a shot.

Ron Rosenfeld Over a year ago

@FlippinPippin Take another look at the "filter" code. I'm pretty sure you could link that to a button and use it the same way. Especially with large amounts of data, it might be useful to see which method executes faster.

Collectives™ on Stack Overflow

Remove rows from a 2d array if value in column is empty

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related