Range?
 

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?

Range?
 

Hi MAB

Try:
Dim LastCell As Range, Rng As Range

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

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


---
Regards,
Norman



"MAB" wrote in message
...
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?

Range?
 

"Norman Jones" wrote in message
...
Hi MAB

Try:
Dim LastCell As Range, Rng As Range

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

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


---
Regards,
Norman

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 )

Range?
 

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


---
Regards,
Norman



"MAB" wrote in message
...

"Norman Jones" wrote in message
...
Hi MAB

Try:
Dim LastCell As Range, Rng As Range

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

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


---
Regards,
Norman

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 )