Selecting a range of rows

  • Thread starter Thread starter Roman Lutkiewicz
  • Start date Start date
R

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
 
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.)
 
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 said:
I meant it should be:

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


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.
 
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
 
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)
Click to expand...
 
Back
Top