Insert cells at end of selection
 

Thanks,

The only problem I am finding is that it is inserting the new row, 2 rows
above the last row with data, not one

"Rick Rothstein" wrote:

This should do what you want, just assign the number of rows of cells in
Columns A through R to insert to the NumberOfBlankRows variable (I used 4
for my example code)...

Sub InsertBlankCellsColumnsAtoR()
Dim LastRow As Long, NumberOfBlankRows
NumberOfBlankRows = 4
LastRow = Range("A3:R1000").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Intersect(Range("A:R"), Rows(LastRow)).Resize(NumberOfBlankRows).Insert
End Sub

--
Rick (MVP - Excel)


"Kirsty" wrote in message
...
Hi,

I have a macro that I want to change to go look in a range of cells
A3:R1000
and find the last row with data.

I then want to step back and insert cells above this row.

Eg, i have data in row 140, so I want to insert cells to get a blank lot
of
cells in row 140 from columns A to R.

Is this possible?

Kirsty

.

Insert cells at end of selection
 

Sorry, have been having computer issues, but I have fixed the problem.

"Kirsty" wrote:

Thanks,

The only problem I am finding is that it is inserting the new row, 2 rows
above the last row with data, not one

"Rick Rothstein" wrote:

This should do what you want, just assign the number of rows of cells in
Columns A through R to insert to the NumberOfBlankRows variable (I used 4
for my example code)...

Sub InsertBlankCellsColumnsAtoR()
Dim LastRow As Long, NumberOfBlankRows
NumberOfBlankRows = 4
LastRow = Range("A3:R1000").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Intersect(Range("A:R"), Rows(LastRow)).Resize(NumberOfBlankRows).Insert
End Sub

--
Rick (MVP - Excel)


"Kirsty" wrote in message
...
Hi,

I have a macro that I want to change to go look in a range of cells
A3:R1000
and find the last row with data.

I then want to step back and insert cells above this row.

Eg, i have data in row 140, so I want to insert cells to get a blank lot
of
cells in row 140 from columns A to R.

Is this possible?

Kirsty

.