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

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" wrote in message
oups.com...
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

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