Excel variable Range

G

Gerry

I receive a number of spreadsheets with different number
of rows. I want to copy a particular calc to a new column
for all rows. I can create a macro to do all this except
I do not know how to tell the macro to paste to the last
row which cam be different in each sheet. Any advice
please.
 
B

Bob Phillips

LastRow can be calculated with

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Gerry

-----Original Message-----
LastRow can be calculated with

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)



Thanks Bob
Click to expand...
But when I have the last row which I can always get how do
I tell my macro to select Col J2 to J(Lastrow) and then
paste
 
B

Bob Phillips

Hi Gerry,

Range("J2").Resize(Cells(Rows.Count, "J").End(xlUp).Row).Select

or better without the select

Range("J2").Resize(Cells(Rows.Count, "J").End(xlUp).Row).copy

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

dim myRng as range

with activesheet
set myrng = .range("J2:J" & .cells(.rows.count,"A").end(xlup).row)
end with

Would use the last cell in column A to determine the last cell to use in column
J.

And if I were populating that range with a formula, I think I'd just do it all
in one statement:

myrng.formula = "=sum(a2:i2)"


If you write the formula with the top cell as a reference, then excel will
adjust the formula when it fills the range.

Just like manually: Select the range, type your formula for the activecell and
hit ctrl-enter.

If you're worried, then lots of times it's easier to see if you use the
..formulaR1C1 style:

myrng.formular1c1 = "=SUM(RC[-9]:RC[-1])"

(Toggle the reference style (tools|options|General tab), steal the manually
entered formula from that cell and paste it into your code. Remember that each
" becomes "".)
 
Top