Trying to insert a specified number of cells

[u742884]

I am trying to select a range called A1item2 which is A15:AK15 then insert a specific number of cells, shifting existing cells down. When I record, I get this:

Application.Goto Reference:="A1Item2"
Range("A15:AK21").Select
Selection.Insert Shift:=xlDown

But of course, it is recording the actual address of cells being selected to insert down. Anyone have any ideas?

 

[Smitty]

How about something like this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> InsertRows()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
        i = Application.InputBox("Enter the number of rows to insert", "Insert Rows", 1, Type:=1)
        
        Range(ActiveCell, ActiveCell.Offset(i - 1)).Insert Shift:=xlDown
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[u742884]

Almost

Thanks for the reply. That almost worked. However, instead of inserting cells beneath the entire A15:AK15 range, it only inserted the cells beneath A15.

 

[Smitty]

My bad, jsut replace the guts witrh:

Range(ActiveCell, ActiveCell.Offset(i - 1)).EntireRow.Insert Shift:=xlDown

The key point being the EntireRow part. Or did you want to keep the range isolated to A:AK only?

Smitty

 

[u742884]

Over shot it

Not quite, and here's the tricky part. I can't insert an entire row b/c there's stuff off to the right starting in column AM that can't have a row inserted into (this is someone else's spreadsheet--I never would have built it this way). I need to insert the cells only below cells A15:AK15.

 

[u742884]

Got it

I made the following change to refer to the A:AK range and it worked. Thanks for your help!


Dim i As Integer
i = Application.InputBox("Enter the number of rows to insert", "Insert Rows", 1, Type:=1)

Application.Goto Reference:="A1Item2"
Range("A1Item2", ActiveCell.Offset(i - 1)).Insert Shift:=xlDown

 

[Smitty]

See if this version does it:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> InsertRows()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
        
        <SPAN style="color:#007F00">'   Insert Below A15:AK15 only</SPAN>
        <SPAN style="color:#00007F">If</SPAN> ActiveCell.Row <= 14 <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Must be below Row 15"
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        Else:
            i = Application.InputBox("Enter the number of rows to insert", "Insert Rows", 1, Type:=1)
            Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + i - 1, 37)).Insert Shift:=xlDown
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty