Selecting a range of rows

[Roman Lutkiewicz]

Hi,

I am just fiddling with some basic VBA stuff. Could I use your assist please?

I am trying to copy a range from Cell A4 up to the last filled Cell -2 rows and then copy it below.

I came up with the Sub

Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A4:A29").Select
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlDown).Select
Range("A30").Select
Selection.Insert Shift:=xlDown

But this is obviously incorrect as A29 is not always 2 rows up from the Selection.End.

Could anyone help with this??

Thanks
Roman

Submitted via EggHeadCafe
Using the LINQ Max Operator
http://www.eggheadcafe.com/tutorial...1714a20d4c36/using-the-linq-max-operator.aspx

 

[Dave Peterson]

Instead of using .select's:

Dim LastRowToCopy as long
Dim Rng as range

with activesheet
lastrowtocopy = .cells(.rows.count,"A").end(xlup) - 2
set rng = .range("A4:A" & lastrowtocopy)

rng.entirerow.copy _
destination:=.cells(lastrowtocopy + 3,"A")
end with

(I didn't know where you were pasting, so I added 3 to that lastrowtocopy.)

 

[Rick Rothstein]

lastrowtocopy = .cells(.rows.count,"A").end(xlup) - 2

** Typo Alert **

Dave accidentally left out the Row property call. The above line should have
read this...

lastrowtocopy = .cells(.rows.count,"A").end(xlup).Row - 2

Rick Rothstein (MVP - Excel)

 

[Roman Lutkiewicz]

Thanks Dave,

I am pasting it just one cell below so I guess the syntax should be:

destination:=.cells(lastrowtocopy + 3,"A")

Hovewer when I run the macro it says that the Variable is not defined stopping at 'destination:'

Could you help?
Thanks
R

Submitted via EggHeadCafe
Install Windows Updates using C# & WUAPI
http://www.eggheadcafe.com/tutorial...b/install-windows-updates-using-c--wuapi.aspx

 

[Roman Lutkiewicz]

I meant it should be:

destination:=.cells(lastrowtocopy + 1,"A")

Submitted via EggHeadCafe
Using the LINQ Max Operator
http://www.eggheadcafe.com/tutorial...1714a20d4c36/using-the-linq-max-operator.aspx

 

[Roman Lutkiewicz]

I meant it should be:

destination:=.cells(lastrowtocopy + 1,"A")

Submitted via EggHeadCafe
C# Dynamic XElement wrapper
http://www.eggheadcafe.com/tutorial...-bda9b6fc8d47/c-dynamic-xelement-wrapper.aspx

 

[Clif McIrvin]

I meant it should be:

destination:=.cells(lastrowtocopy + 1,"A")

Dave's code was

rng.entirerow.copy _
destination:=.cells(lastrowtocopy + 3,"A")

Which is all a single line ... note the [ _] [ space underscore ] at the
end of the first line ... that is the line continuation character.

if you don't have the [ _ ] in place you will get an error.

 

[Gord Dibben]

Did you make the correction to the code that Rick pointed out?

Do you really want to overwrite the last two rows of the original data?

Maybe you should stick to Dave's + 3


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Thanks for the correction, Rick.

** Typo Alert **

Dave accidentally left out the Row property call. The above line should have
read this...

lastrowtocopy = .cells(.rows.count,"A").end(xlup).Row - 2

Rick Rothstein (MVP - Excel)