Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows by vba
Dave,
When you say it "makes life easier..." is that because when it goes forward the rows start getting offset in an increasing count from the count originally showing? I.e., once I figured out why I wasn't getting it to work, I increased my 'to' from 4 to 20, and by the last one it'd jumped up one from the 24-3 spacing I wanted to a 21-24 spacing. (I hope that makes sense.) I've changed it to go backwards, to see how it works. I'll let you know once it finishes the initial run. It looks as though it'll be a while as my home system is 8 years old, and runs likes a turtle, snail's pace. "Dave Peterson" wrote: When you're inserting/deleting rows, it sometimes make life much easier to work from the bottom up: This: For i = 1 To 40 Step 8 Becomes: For i = 40 to 1 step -8 I'm not sure how that fits into the rest of your code, though. Maybe... Sub InsertRow() Dim MyRange As Range dim i as long Set MyRange = Nothing On Error Resume Next Set MyRange = Application.InputBox(Prompt:="Pick the row to start at.", _ Type:=8).Rows(1) on error goto 0 if myRange is nothing then exit sub end if Workbooks.Application.ScreenUpdating = False For i = myrange.row To 4 Step -8 rows(i).EntireRow.Insert Next i End Sub SteveDB1 wrote: Hi all. Hope everyone is enjoying their respective memorial day weekend. I have a large file that I've made, and want to insert a series of rows by vba. I thought the following would work, but I've missed something, and I cannot quite figure out where I've gone wrong. I've tried a number of variations, and nothing works. I.e., they'll either only insert one row, but no more, or no rows at all. I'd thought that some variation of: for i = 1 to n step X would work, but it hasn't. Sub InsertRow() ' ' InsertRow Macro ' make this a auto-insert macro to insert my rows for me. ' it'll definitely save time. Dim MyRange As Range Set MyRange = Nothing On Error Resume Next Set MyRange = Application.InputBox(Prompt:="Pick the row to start at.", Type:=8).Rows(1) Workbooks.Application.ScreenUpdating = False For i = 1 To 4 Step 8 MyRange(i).EntireRow.Insert (xlShiftDown) Next i 'On Error GoTo 0 'If MyRange Is Nothing Then 'Exit Sub 'user hit cancel. 'End If Savechanges = True End Sub -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
Insert page breaks every 50 rows but do not include hidden rows | Excel Programming | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Copy Rows and insert these rows before a page break | Excel Programming |