VBA - How to remove empty cell from array

Question

I've got a range that I'm inserting into an array. after that, I'm trying to get only the unique Items but I get them with one (unique) empty value (There are few blanks in my range).

How can I get rid of the empty array cell?

This is my code:

Sub uniqueArray()

Dim myList As Variant
uniqueNameList As Variant

myList = Range("C2:C100").Value
uniqueNameList = WorksheetFunction.Unique(myList)

End sub

for example:

If you have 365 version you could also use the build in =unique function. — ceci
– ceci, Commented May 15, 2021 at 14:09

ceci · Accepted Answer · 2021-05-16 10:07:11Z

1

This will do what you want:

    Option Explicit
    Sub remBlanks()
        Dim arr, arr2, nrRw As Long
        nrRw = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row - 1 'count source to arr without header
        arr = Sheet2.Range("C2:C" & nrRw).Value2 'add source to arr without header
        
        Dim j As Long, jj As Long: jj = 1
        ReDim arr2(1 To UBound(arr), 1 To UBound(arr, 2))
        For j = 1 To UBound(arr)
            If arr(j, 1) <> "" Then 'remove blanks
                arr2(jj, 1) = arr(j, 1)
                jj = jj + 1
            End If
        Next j
        With Sheet2
            .Range(.Cells(2, 4), .Cells(UBound(arr2), 4)).Value2 = arr2 'dumb to sheet
        End With
    End Sub

answered May 16, 2021 at 10:07

ceci

7841 gold badge5 silver badges15 bronze badges

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

Comments

Kin Siang · Accepted Answer · 2021-05-15 13:45:10Z

0

You can just add second function to check for empty value and remove it, like following:

Dim i As Long

For i = 1 To 100
    If IsEmpty(cells(i,3).Value) Then
    Cells(i, 3).Delete
    End If
Next

However, it will be better to check for used row in the sheet to optimize the function.

answered May 15, 2021 at 13:45

Kin Siang

2,6992 gold badges6 silver badges8 bronze badges

2 Comments

User1 Over a year ago

That's not helping me because the work was done with the for loops on Excel cells and not with VBA arrays.

Kin Siang Over a year ago

VBA array is referring to excel cells.... . In your case Range("C2:C100"), is equal to cells(2,3) untill cells(100,3), to delete the row, you need break the range into for loop, then should be able to solve your problem...

Collectives™ on Stack Overflow

VBA - How to remove empty cell from array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related