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 )
|