Select non empty cells

[Mika]

Hi guys/gals

I want to select (VBA) the non empty cells within a given range, like
given a1:c4 only the cells below, that are non empty, will be
selected. I tried combinations of intersect with specialcells but
unsuccessfully.

a1 c1
b2
a3 c3
a4b4

Thanks for your time,
Mika

 

[Zone]

Mika, I can't remember who in this newsgroup came up with this little gem.
But it was so clever that I remembered it and I'll pass it on to you! James

Sub Sel()
Dim rgStr As String, c As Range
For Each c In Range("a1:c5")
If c <> "" Then
If rgStr = "" Then
rgStr = c.Address
Else
rgStr = rgStr & "," & c.Address
End If
End If
Next c
Range(rgStr).Select
End Sub

 

[Mika]

Thanks,

It does the trick for small ranges but not for big ones, when the
string exceeds excel max size, but I think I can find the workaround.
Still, it should be great if it could be done without checking every
cell using in built functions... ah well...

thanks
mika