Range?

M

MAB

How do I specify a range from say "A1" down to the last non empty cell. I
know it should contain something like xlDown but I cant figure the whole
syntax.

thx.

And why nobody answered by last post?
 
N

Norman Jones

Hi MAB

Try:
Dim LastCell As Range, Rng As Range

Set LastCell = Cells(Rows.Count, "A").End(xlUp)

Set Rng = Range(Range("A1"), LastCell)
 
M

MAB

Norman Jones said:
Hi MAB

Try:
Dim LastCell As Range, Rng As Range

Set LastCell = Cells(Rows.Count, "A").End(xlUp)

Set Rng = Range(Range("A1"), LastCell)
Click to expand...

Thanks. This will work but has a problem. This will return the range from A1
to the cell below which all cells are empty. But what if there are further
empty cells above that cell? I want the range to not contain any empty cell
so it should
start from A1 and end when the first blank ( empty ) cell appears ( actually
one cell above the empty cell )
 
N

Norman Jones

Hi MAB,

Try:

Set rng = Range(Range("A1"), Range("A1").End(xlDown))

However for a more robust approach:

Sub Tester01()
Dim rng As Range
Dim StartCell As Range

Set StartCell = Range("A1") '<<== CHANGE TO SUIT

If Not IsEmpty(StartCell) Then
If Not IsEmpty(StartCell.Offset(1)) Then
Set rng = Range(StartCell, StartCell.End(xlDown))
End If
Else
Set rng = StartCell
End If

MsgBox rng.Address
End Sub
 
Top