save a range to copy for later

[John]

I want to select a range starting with active cell to the last row and copy
it... but first I have to clear the copy range starting in A3... then paste
in A3. can someone help?

first = ActiveCell.Address
col = ActiveCell.Column
lastrow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
lst = "r" & lastrow & "c" & col

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


Range(first, lst).Copy
Range("a3").Select
Selection.PasteSpecial Paste:=xlValue

 

[Dave Peterson]

And the activecell won't be in column A, right???

dim TopCell as range
dim BotCell as range

with activesheet
if activecell.column = 1 then
msgbox "Not from column A!"
else
set topcell = activecell
set botcell = .cells(.rows.count,topcell.column).end(xlup)

if topcell.row > botcell.row then
msgbox "this doesn't look right!"
else
.range("a3:a" & .rows.count).clearcontents
.range(topcell, botcell).copy
.range("A3").pastespecial paste:=xlpastevalues
end if
end if
end with

 

[John]

Nevermind...
lastcell = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Address

rather than .row