MrExcel.com - Your One Stop for Excel Tips & Solutions

Code not working

Posted by Keith on July 21, 2000 2:05 AM

I am trying to add a piece of code to an existing one.
It is not working.

If anyone knows how to do this - they're a legend.

The problem is that I want the Macro to do (NB Rows are not static):

Find the last but one Row (eg29)
Insert new Row
Find the last but one Row (now 30)
Copy up to the blank Row (29)
Find the last Row (31)
Cut
Go up one Row (30)
Paste


I have tried this (to you experts it'll probably look bad).

LastRow2 = Range("A65536").End(x2Up).Row + 2
Rows(LastRow2).Select
Selection.Insert Shift:=xlDown
LastRow2 = Range("A65536").End(x2Up).Row + 2
Rows(LastRow2).Select
Selection.Copy Destination:=Rows
LastRow = Range("A65536").End(xlUp).Row + 1
Rows(LastRow).Select
Selection.Cut
LastRow2 = Range("A65536").End(x2Up).Row + 2
Rows(LastRow2).Select
ActiveSheet.Paste

This code works BUT the rows WILL change so basically useless.

Rows("29:29").Select
Selection.Insert Shift:=xlDown
Rows("30:30").Select
Selection.Copy Destination:=Rows("29:29")
Rows("31:31").Select
Selection.Cut
Rows("30:30").Select
ActiveSheet.Paste


Help, Keith

Posted by Ada on July 21, 0100 3:02 AM

Keith

Set lastrow2 = Range("A65536").End(xlUp).Offset(-1, 0).EntireRow
With lastrow2
.Copy
.Insert Shift:=xlDown
.Offset(1, 0).Delete
End With
Range("A65536").End(xlUp).EntireRow.Select

Ada

Posted by Ada on July 21, 0100 3:36 AM

Correction

Keith
Sorry, I posted the wrong code. Should be :-

Set lastrow2 = Range("A65536").End(xlUp).Offset(-1, 0).EntireRow
With lastrow2
.Copy
.Insert Shift:=xlDown
.Offset(1, 0).Copy Destination:=lastrow2
.Offset(1, 0).Delete
End With
Range("A65536").End(xlUp).EntireRow.Select

HOWEVER :-
The above does the steps specified in your message but the end result is that the data at the end is exactly the same as the starting data. What needs to be revised in the steps below?

Find the last but one Row (eg29)
Insert new Row
Find the last but one Row (now 30)
Copy up to the blank Row (29)
Find the last Row (31)
Cut
Go up one Row (30)
Paste

Ada

Posted by Keith on July 21, 0100 4:49 AM

Works fine

Ada,

Believe it or not it is exactly what I wanted it do.
If your still confused by this strange request, it is to update a chart, by moving,cutting and pasting the chart range moved with the rows.

Cheers,

keith