Select range from ActiveCell do to Lastcell

[Aussie Bob C]

From Leith Ross 2/7/2006

Dim EndCell As Range
Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
ActiveSheet.Range(ActiveCell, EndCell).ClearContents

If I change the A to an N it selects a range from ActiveCell down to last
entry in Column N.
My ActiveCell is in Column A but Column N may not have data down to the
last cell as in Column A

How do I select a range of cells from an ActiveCell in column A across to
Column N and down to last data cell in column A

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.

 

[Mike H]

Hi,

Try this with the usual caveat that it is very unlikely you need to select
the range to do what you want. The last line could simply be

MyRangeA.ClearContents


Dim MyRangeA As Range
Dim LastRowA As Long
LastRowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRangeA = Range("A" & ActiveCell.Row & ":N" & LastRowA)
MyRangeA.Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[OssieMac]

Hello Aussie Bob,

Whether you use the If/Else/EndIf is up to you but I should think that you
would not want code selecting the range if you have not previously selected a
cell in the correct column.

Having said that, normally with code it is not necessary to actually select
ranges but not knowing what you are doing with the code it is hard to advise
the best way for your particular case.

Sub SelectSpecific()

Dim lastRow As Long

With ActiveSheet
If ActiveCell.Column = Columns("A").Column Then
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(ActiveCell, .Cells(lastRow, "N")).Select
Else
MsgBox "Activecell not in column A"
End If
End With

End Sub

 

[Roger Govier]

Hi Bob

Dim lr as long, fr as long
with activesheet
fr=activecell.row
lr = cells(Rows.count("A").End(Xlup).Row
range("A" & fr & ":N" & lr).clearcontents
end with

 

[Mike Fogleman]

This should do it:

Sub test()
Dim EndCell As Range
Set EndCell = Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp), "N")
ActiveSheet.Range(ActiveCell, EndCell).ClearContents
End Sub

Mike F

 

[Aussie Bob C]

Hi OssieMac

Thanks for the code with the If/Else/Endif safety added.
Just what I needed.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.