Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting multiple non-consecutive rows
I have spreadsheet populated with 200 names. I need to insert 2 blank rows
between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting multiple non-consecutive rows
Hi Piper,
I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Try: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim LRow As Long Dim i As Long Dim CalcMode As Long Set WB = Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With LRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LRow To 1 Step -1 Rows(i + 1).Resize(2).Insert Next i XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<============= If you are not familiar with macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman "piper1963" wrote in message ... I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting multiple non-consecutive rows
Norma,
I have a similar problem to Piper's. But this solution you gave is Greek to me, as is the information in McRitchie's page for macros. Is there a simpler way? Or can you translate this into "English?" "Norman Jones" wrote: Hi Piper, I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Try: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim LRow As Long Dim i As Long Dim CalcMode As Long Set WB = Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With LRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LRow To 1 Step -1 Rows(i + 1).Resize(2).Insert Next i XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<============= If you are not familiar with macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman "piper1963" wrote in message ... I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting multiple non-consecutive rows
Maybe this would be a little easier.
Sub InsertRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long numRows = 2 For r = 2000 To 1 Step -1 ActiveSheet.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub First...create a backup copy of your original workbook. Now...with your workbook open To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project by name and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook and worksheet you want altered. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo Gord Dibben MS Excel MVP On Wed, 21 Jun 2006 15:01:02 -0700, Merna wrote: Norma, I have a similar problem to Piper's. But this solution you gave is Greek to me, as is the information in McRitchie's page for macros. Is there a simpler way? Or can you translate this into "English?" "Norman Jones" wrote: Hi Piper, I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Try: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim LRow As Long Dim i As Long Dim CalcMode As Long Set WB = Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With LRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LRow To 1 Step -1 Rows(i + 1).Resize(2).Insert Next i XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<============= If you are not familiar with macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman "piper1963" wrote in message ... I have spreadsheet populated with 200 names. I need to insert 2 blank rows between each name. I know how to manually insert blank rows. But, is there a quicker, more efficient way to do this? Thank you! Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i wrap rows to form multiple rows per row to fit on 1 sheet? | Excel Discussion (Misc queries) | |||
Inserting rows in multiple workbooks | Excel Discussion (Misc queries) | |||
Inserting Rows w/formulas question | Excel Worksheet Functions | |||
Convert multiple columns to rows | Excel Worksheet Functions | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel |