Selecting varying length range

[Guest]

I have 22 different files for which I want to use the same procedure (with
few modifications). The procedure opens a specific file (different for each
file containing the macro), inserts columns, inserts formulas, and then fills
the formulas down to the last cell in it's column. (Maunally I would simply
double-click the autofill handle and Excel would copy down to the last cell
based on the column to the left.) When I record the actions, the ending cell
is absolute, so I've had to go into each of the 22 modules and manually enter
the ending cells for each file. I know there's a way to set the last cell I
need as a variable, but even after reading other posts on this subject I
can't get it to work.

Thanks to all for your suggestions.

 

[Don Guillett]

You should always post your coding efforts for comments and suggestions. To
find the last row use the longest column this. Then refer to it instead of
the row in the constant

lastrow=cells(rows.count,"a").end(xlup).row
range("a2:a22")
range("a2:a" & lastrow)

 

[Guest]

Thanks for helping me be a "better poster"! Here's the code. Column H is
empty, so with my autofill, I'm relying on the last non-empty cell in Column
G, which is a different cell in each file.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]"
Selection.AutoFill Destination:=Range("H2:H1882")
Range("H2:H1882").Select

 

[Don Guillett]

So, try adapting my suggestion

lastrow=cells(rows.count,"g").end(xlup).row
Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]"
Range("h2").AutoFill Destination:=Range("H2:H" & lastrow)



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks for helping me be a "better poster"! Here's the code. Column H is
empty, so with my autofill, I'm relying on the last non-empty cell in
Column
G, which is a different cell in each file.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]"
Selection.AutoFill Destination:=Range("H2:H1882")
Range("H2:H1882").Select

--
TIA, Nan

You should always post your coding efforts for comments and suggestions.
To
find the last row use the longest column this. Then refer to it instead
of
the row in the constant

lastrow=cells(rows.count,"a").end(xlup).row
range("a2:a22")
range("a2:a" & lastrow)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Guest]

Thank you so much for helping me over this hump. I needed this snipet in
several different places and it works perfectly every time!
--
TIA, Nan

So, try adapting my suggestion

lastrow=cells(rows.count,"g").end(xlup).row
Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]"
Range("h2").AutoFill Destination:=Range("H2:H" & lastrow)



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks for helping me be a "better poster"! Here's the code. Column H is
empty, so with my autofill, I'm relying on the last non-empty cell in
Column
G, which is a different cell in each file.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]"
Selection.AutoFill Destination:=Range("H2:H1882")
Range("H2:H1882").Select

--
TIA, Nan

You should always post your coding efforts for comments and suggestions.
To
find the last row use the longest column this. Then refer to it instead
of
the row in the constant

lastrow=cells(rows.count,"a").end(xlup).row
range("a2:a22")
range("a2:a" & lastrow)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have 22 different files for which I want to use the same procedure
(with
few modifications). The procedure opens a specific file (different for
each
file containing the macro), inserts columns, inserts formulas, and then
fills
the formulas down to the last cell in it's column. (Maunally I would
simply
double-click the autofill handle and Excel would copy down to the last
cell
based on the column to the left.) When I record the actions, the
ending
cell
is absolute, so I've had to go into each of the 22 modules and manually
enter
the ending cells for each file. I know there's a way to set the last
cell
I
need as a variable, but even after reading other posts on this subject
I
can't get it to work.

Thanks to all for your suggestions.

 

[Don Guillett]

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thank you so much for helping me over this hump. I needed this snipet in
several different places and it works perfectly every time!
--
TIA, Nan

So, try adapting my suggestion

lastrow=cells(rows.count,"g").end(xlup).row
Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]"
Range("h2").AutoFill Destination:=Range("H2:H" & lastrow)



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks for helping me be a "better poster"! Here's the code. Column H
is
empty, so with my autofill, I'm relying on the last non-empty cell in
Column
G, which is a different cell in each file.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]"
Selection.AutoFill Destination:=Range("H2:H1882")
Range("H2:H1882").Select

--
TIA, Nan


:


You should always post your coding efforts for comments and
suggestions.
To
find the last row use the longest column this. Then refer to it
instead
of
the row in the constant

lastrow=cells(rows.count,"a").end(xlup).row
range("a2:a22")
range("a2:a" & lastrow)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have 22 different files for which I want to use the same procedure
(with
few modifications). The procedure opens a specific file (different
for
each
file containing the macro), inserts columns, inserts formulas, and
then
fills
the formulas down to the last cell in it's column. (Maunally I
would
simply
double-click the autofill handle and Excel would copy down to the
last
cell
based on the column to the left.) When I record the actions, the
ending
cell
is absolute, so I've had to go into each of the 22 modules and
manually
enter
the ending cells for each file. I know there's a way to set the
last
cell
I
need as a variable, but even after reading other posts on this
subject
I
can't get it to work.

Thanks to all for your suggestions.