Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simultaneouly insert of rows or col. - Ajit | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |