Setting ranges

[Scottie]

how would i set a range for all non empty cells in column
a on a sheet?

 

[Frank Kabel]

Hi
you may try the following to select all non blank cells in column A
Sub foo2()

Dim rng As Range
Dim cell As Range
Dim target_rng As Range
Set rng = Range("A:A")
For Each cell In rng
If cell.Value <> "" Then
If target_rng Is Nothing Then
Set target_rng = cell
Else
Set target_rng = Union(target_rng, cell)
End If
End If
Next
target_rng.Select
End Sub

 

[Bob Phillips]

Scottie,

Something like

Set myRange = columns(1).specialcells(xlcelltypeblanks)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robert McCurdy]

Hi Scottie.

Sub findAll1()
Dim Str As String, c As Range
For Each c In Range("D218")
If c.Value <> "" Then Str = Str & "," & c.Address
Next c
Range(Mid(Str, 2, Len(Str))).Select
End Sub

Or just sort the column?

Regards Robert