CODEWITHSUNDEEP
excelvba
Joseph Lion
Joseph Lion

Reputation: 53

In an excel range - ignore empty cells

I'm concatenating using an excel range:

Set rng = Range("A1:A8")

If one of the cells withing the range is empty it adds a single space.

How do you stop this space from being added?

Upvotes: 2

Views: 327

Answers (2)

Vitaliy Prushak
Vitaliy Prushak

Reputation: 1162

If you are concatenating range then a simple loop may help:

Dim rng As range, cell As range

For Each cell In range("A1:A8") ' looking through the desired range
    If Not cell = "" Then ' if the cell is not empty we have to add it to range
        If Not rng Is Nothing Then ' if the range already has some cells in it
            Set rng = Union(rng, cell) ' we have to add the cell to an existing range
        Else ' if the range does not yet exists
            Set rng = cell ' create it and add the first cell
        End If
    End If
Next

Upvotes: 0

JvdV
JvdV

Reputation: 75990

Assuming you have got constants in those non-empty cells:

Set rng = Range("A1:A8").SpecialCells(2)

Unfortunately, non-contiguous ranges make that we can't put rng into an array or use it in a Join operation. You'll have to loop each cell in the range.

However, I would utilize Application.Trim instead of setting a range. This way we can work through memory (array) instead of a more sluggish Range object reference:

Sub Test()

Dim arr As Variant: arr = [TRANSPOSE(A1:A8)]
Debug.Print Application.Trim(Join(arr, " "))

End Sub

Or, if you don't want to utilize .Evaluate:

Sub Test()

Dim arr As Variant: arr = Range("A1:A8")
With Application
    Debug.Print .Trim(Join(.Transpose(.Index(arr, 0, 1)), " "))
End With

End Sub

Note-1: We need Transpose to return a 1D-array to be able to use in Join.

Note-2: If one has got access to TEXTJOIN function, there is no need for all this, but you can utilize that function in an array form.

Upvotes: 1

Related Questions