Insert cells at end of selection

  • Thread starter Thread starter Kirsty
  • Start date Start date
K

Kirsty

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
 
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
 
Sub FindLastRowEnterBlankBefore()

Dim rng As Range
Set rng = Range(Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1),
ActiveCell.SpecialCells(xlLastCell))
rng.Offset(-1, 0).Insert Shift:=xlDown
End Sub
 
There is a problem with your approach... it is your use of the xlLastCell
argument to the SpecialCells property. The "last cell" that gets returned is
the last cell of the UsedRange which may or may not be the last cell with
data in it. Try this experiment to see why. Type something in A1 (it doesn't
matter what) and then copy it across to, say, G1. Now select the range A1:G1
and copy it down to, say, Row 15. Now go to the Immediate Window of the VB
editor and execute this line in it...

? ActiveCell.SpecialCells(xlLastCell).Address

It should print out $G$15 as expected. Okay, go back to the worksheet, click
into a cell outside of the data area, say cell M25, then type anything (it
doesn't matter what) into that cell and hit the Enter key. Now click back
into that cell and press the Delete key. Now go back to the Immediate Window
and re-execute the above statement. It should now print $M$25... the address
of the cell you just deleted the last entry from; which, of course, is not
the last row of data.

Even without the above problem, I also note that your code assumes the last
cell on the worksheet is in columns A through R. This is not necessarily the
case. The OP asked for blank cells to be inserted above the last row of data
in columns A through R only. If there is data in columns beyond Row R and/or
in rows at or beyond the last row of data within columns A through R, then
your code will affect them as well. In other words, even if the SpecialCells
method you used worked correctly, you would still be identifying the wrong
last cell for such a situation.
 
There is a problem with your approach... it is your use of the xlLastCell
argument to the SpecialCells property. The "last cell" that gets returned is
the last cell of the UsedRange which may or may not be the last cell with
data in it. Try this experiment to see why. Type something in A1 (it doesn't
matter what) and then copy it across to, say, G1. Now select the range A1:G1
and copy it down to, say, Row 15. Now go to the Immediate Window of the VB
editor and execute this line in it...

? ActiveCell.SpecialCells(xlLastCell).Address

It should print out $G$15 as expected. Okay, go back to the worksheet, click
into a cell outside of the data area, say cell M25, then type anything (it
doesn't matter what) into that cell and hit the Enter key. Now click back
into that cell and press the Delete key. Now go back to the Immediate Window
and re-execute the above statement. It should now print $M$25... the address
of the cell you just deleted the last entry from; which, of course, is not
the last row of data.

Even without the above problem, I also note that your code assumes the last
cell on the worksheet is in columns A through R. This is not necessarily the
case. The OP asked for blank cells to be inserted above the last row of data
in columns A through R only. If there is data in columns beyond Row R and/or
in rows at or beyond the last row of data within columns A through R, then
your code will affect them as well. In other words, even if the SpecialCells
method you used worked correctly, you would still be identifying the wrong
last cell for such a situation.
Click to expand...

Thy for pointing that out, i honestly hadn't thought of that, the task
just seemed so easy that even I could do it. Turns out, it wasn't ;)
 
Thanks Rick,

The only problem is that it is inserting the row, two rows above the last
row in teh range with data, not above the last row.

The last row has a merged cell A:H. Will this affect it?

Kirsty
 
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
 
Back
Top