Insert more than 1 Row Syntax
 

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.


--
blazzae
------------------------------------------------------------------------
blazzae's Profile: http://www.excelforum.com/member.php...o&userid=24981
View this thread: http://www.excelforum.com/showthread...hreadid=385147

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"blazzae" wrote in
message ...

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.


--
blazzae
------------------------------------------------------------------------
blazzae's Profile:
http://www.excelforum.com/member.php...o&userid=24981
View this thread: http://www.excelforum.com/showthread...hreadid=385147

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.


--
blazzae
------------------------------------------------------------------------
blazzae's Profile: http://www.excelforum.com/member.php...o&userid=24981
View this thread: http://www.excelforum.com/showthread...hreadid=385147

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).



blazzae wrote:

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.

--
blazzae
------------------------------------------------------------------------
blazzae's Profile: http://www.excelforum.com/member.php...o&userid=24981
View this thread: http://www.excelforum.com/showthread...hreadid=385147

--

Dave Peterson

Thanks for the pick-up, Dave !
Tried out your sub, runs great.
The OP should be pleased ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

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

cheers


--
blazzae
------------------------------------------------------------------------
blazzae's Profile: http://www.excelforum.com/member.php...o&userid=24981
View this thread: http://www.excelforum.com/showthread...hreadid=385147