Re: Excel VBA Adjustment To Insert Rows Code

[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

 

[mathew]

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

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

 

[Ken Wright]

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.

 

[Ken Wright]

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.

 

[Ken Wright]

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.