CODEWITHSUNDEEP
excelvba
Aurelius
Aurelius

Reputation: 485

Fill array with range values but not if cell is blank

I have a range full of data that I want to put into an array. However, there are blank cells in this data and I do not want to include them in my array.

I have created the below but It is not working, I've tried to put a msgbox "if has been triggered" into the IF statement and no msgbox has appeared when running this which says to me the `IF statement isn't actually triggering.

Dim rngAB As Range: Set rngAB = wsWeights.Range("AB4:AB" & wsWeights.Cells(Rows.count, "AB").End(xlUp).Row)
Dim arr() As Variant

k = 1
For Each cell In rngAB
    If cell.Value <> vbNullString Then
        arr(k, 1) = cell.Value
    k = k + 1
    End If
Next

Where am I going wrong here?

Upvotes: 0

Views: 5757

Answers (2)

Ambie
Ambie

Reputation: 4977

The main point here is what do you mean by "blank"? A cell which is Empty, for example, is different from one which contains vbNullString.

Only you will know exactly what blank means for your project, but some common ways of checking are:

If IsEmpty() Then ...
If Len(value) > 0 Then ...
If Len(Trim(value)) > 0 Then ...
If Trim(value & vbNullString) = vbNullString Then ...

In terms of your code, you do need to dimension your array before you populate it. One way, but by no means the only way, of achieving your task would be as follows (I've assumed the IsEmpty() case):

Dim r As Long
Dim data As Variant, n As Variant
Dim col As Collection
Dim arr() As Variant

'Read sheet data into array.
With Sheet1
    data = .Range(.Range("AB4"), .Cells(.Rows.Count, "AB").End(xlUp)).Value2
End With

'Acquire the non-empty array indexes.
Set col = New Collection
For r = 1 To UBound(data, 1)
    'We're using the IsEmpty() function.
    If Not IsEmpty(data(r, 1)) Then col.Add r

    'Uncomment any of the below if you prefer to use them instead:
    'If Len(data(r, 1)) > 0 Then col.Add r
    'If Trim(data(r, 1) & vbNullString) = vbNullString Then col.Add r
Next

'Dimension array to size of non-empty list.
ReDim arr(1 To col.Count, 1 To 1)

'Populate the array
r = 1
For Each n In col
    arr(r, 1) = data(CLng(n), 1)
    r = r + 1
Next

Upvotes: 1

Plagon
Plagon

Reputation: 3139

So this should work: 

Sub FillArrayNoBlanks()
Dim ws As Worksheet, Cell As Range
Dim rngAB As Range, k As Long: k = 0
Dim arr() As Variant

Set ws = Worksheets(1)
With ws
    Set rngAB = .Range("AB4:AB" & .Cells(.Rows.Count, "AB").End(xlUp).Row)
End With

For Each Cell In rngAB
    If Cell.Value <> vbNullString Then
        ReDim Preserve arr(k)
        arr(k) = Cell.Value
        k = k + 1
    End If
Next
End Sub

So whats missing are some definitions, but most importantly the array does not define its size just by putting in some values. In VBA you have to use ReDim to define the size of the Array, Preserve is to keep the values already in the Array. If you dont really need it for something else, i wouldnt recommend using a 2 dimensional array, because you can only ReDim the last dimension of an Array.

Upvotes: 1

Related Questions