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

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

--
Regards
Frank Kabel
Frankfurt, Germany

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

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)

"Scottie" wrote in message
...
how would i set a range for all non empty cells in column
a on a sheet?

Hi Scottie.

Sub findAll1()
Dim Str As String, c As Range
For Each c In Range("D2:D18")
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

"Scottie" wrote in message ...
how would i set a range for all non empty cells in column
a on a sheet?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004