Insert more than 1 Row Syntax

B

blazzae

Hi all,

If the Syntax to insert 1 row is ( macro) :

Selection.EntireRow.Insert

Then what is the correct Syntax to insert more than 1 row ?
Let's say there were 5 rows.

I though it would be:

Selection.EntireRow.Insert,5)

Thankyou.
 
M

Max

From tinkering around with the code produced by the macro recorder,
something like this seems to work:

Range("a1:a5").EntireRow.Insert
 
B

blazzae

Thanks for reply.

It can't be from cell range

It does a Find with this code:

Sub FindWhat()

Cells.Find(What:="Here !", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate ' back to new position ready to paste

' inserts 5 rows
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert

End Sub

But I actualy need to add 30 Rows from the "Here !" location.

The location could be anywhere in sheet.
 
D

Dave Peterson

How about...

Option Explicit
Sub FindWhat()

Dim FoundCell As Range
Dim NumberToInsert As Long

NumberToInsert = 30

With ActiveSheet

Set FoundCell = .Cells.Find(What:="Here !", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Offset(1, 0).Resize(NumberToInsert).EntireRow.Insert
End If

End Sub

I did add it after the FoundCell (.offset(1,0) can be removed if you want).
 
M

Max

Thanks for the pick-up, Dave !
Tried out your sub, runs great.
The OP should be pleased ..
 
B

blazzae

Thanks,
I'll be replacing it with the 30 lines of code over the next few day
and see how it goes.

One I'll mess with is where it say's "show message box", it might tur
out to be a "what if" or "find next then" etc, but I have to try it a
a script first.
Will let you know

cheer
 
Top