Re: Excel VBA Adjustment To Insert Rows Code

  • Thread starter Thread starter Ken Wright
  • Start date Start date
K

Ken Wright

Probably kinda kludgy because I loop the code each time to insert the number of rows you want -
Won't impact time too much unless you are thinking of adding thousands of rows, but I have no
doubt one of the VBA gurus will point out how I can speed it up. The routine prompts you for a
number of rows, but on the assumption that most of the time you will just want a single row,
leaving the response blank will give a single row, rather than having to type in a number. The
dialog box will prompt you anyway.

Sub ExtendRange2()

Dim lrow As Long
Dim lrow1 As Long
Dim lrow2 As Long

ans = InputBox("How many rows do you want to insert?" & vbCrLf _
& "Leave blank & Hit OK for just 1 row")

If ans = "" Then
ans = 1
End If

Application.ScreenUpdating = False

For x = 1 To ans

lrow = Cells(Rows.Count, "A").End(xlUp).Row

lrow1 = lrow - 1
lrow2 = lrow - 2

Rows(lrow1).Insert Shift:=xlDown

Range("A" & lrow2, "AO" & lrow2).AutoFill _
Destination:=Range("A" & lrow2, "AO" & lrow1), Type:=xlFillDefault

On Error Resume Next
Range("A" & lrow1, "AO" & lrow1).SpecialCells(xlCellTypeConstants, 23).ClearContents

Next x
Application.ScreenUpdating = True

End Sub
 
Cheers Ken
Works well.
I thought it would be yourself who provided this fix.
Thank you
Mathew
PS One question! How to set the default of "How many rows
do you want to insert?...
from 1 to 10 ?
Mathew
-----Original Message-----
Probably kinda kludgy because I loop the code each time
Click to expand...
to insert the number of rows you want -
Won't impact time too much unless you are thinking of
Click to expand...
adding thousands of rows, but I have no
doubt one of the VBA gurus will point out how I can speed
Click to expand...
it up. The routine prompts you for a
number of rows, but on the assumption that most of the
Click to expand...
time you will just want a single row,
leaving the response blank will give a single row, rather
Click to expand...
than having to type in a number. The
dialog box will prompt you anyway.

Sub ExtendRange2()

Dim lrow As Long
Dim lrow1 As Long
Dim lrow2 As Long

ans = InputBox("How many rows do you want to insert?" & vbCrLf _
& "Leave blank & Hit OK for just 1 row")

If ans = "" Then
ans = 1
End If

Application.ScreenUpdating = False

For x = 1 To ans

lrow = Cells(Rows.Count, "A").End(xlUp).Row

lrow1 = lrow - 1
lrow2 = lrow - 2

Rows(lrow1).Insert Shift:=xlDown

Range("A" & lrow2, "AO" & lrow2).AutoFill _
Destination:=Range("A" & lrow2, "AO" & lrow1), Type:=xlFillDefault

On Error Resume Next
Range("A" & lrow1, "AO" & lrow1).SpecialCells
Click to expand...
(xlCellTypeConstants, 23).ClearContents
 
In the code it says

If ans = "" Then
ans = 1
End If

which simply says that if no answer is given make ans = the default of 1. Just change it to 10.

Don't forget to change the text in the prompt as well though.
 
Horses for courses though. Dave's is far more efficient than mine ( Me being a VBA newbie :-> ),
and if you were building an app for a company and needed to cut processing time to the bone, you
would want to gain every bit of efficiency you could get. For personal use for just a few rows
though, you probably wouldn't notice much difference either way.
 
Change

lrow = Cells(Rows.Count, "A").End(xlUp).Row
to
lrow = Cells(Rows.Count, "I").End(xlUp).Row

You don't have any other data in that column below the table do you.
 
Back
Top