Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68,
Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
Try something like the following: Sub AAA() Dim WS As Worksheet Set WS = Worksheets("Sheet1") With WS .Rows(221).Resize(8).Insert .Rows(183).Resize(8).Insert .Rows(145).Resize(8).Insert .Rows(107).Resize(8).Insert .Rows(69).Resize(8).Insert .Rows(31).Resize(8).Insert End With End Sub Note that it works from the bottom (highest row number) upward (to lowest row numbers) and that the row number are 1 greater than the row after which you want to insert the new rows. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 21 Dec 2009 13:28:01 -0800, Deb wrote: To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68, Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
The best way to learn this stuff is to record the whole procedure...
Micky "Deb" wrote: To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68, Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
This will work too. Select the cells in the first column of the range where
you want to insert the rows. Sub EnterEightRows() x = 0 For Each cell In Selection.Cells x = x + 1 If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert Next cell End Sub "Deb" wrote: To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68, Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
Thank you, Erin. This works fantastically. I can't thank you enough. It will
save me so much time when before I was doing this step manually. Just curious, will this macro work for whatever length of data that I may have, just run continuous until there is no more data to manipulate? "Erin Searfoss" wrote: This will work too. Select the cells in the first column of the range where you want to insert the rows. Sub EnterEightRows() x = 0 For Each cell In Selection.Cells x = x + 1 If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert Next cell End Sub "Deb" wrote: To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68, Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting numerous blank lines between specific rows of data
The posted macro requires you to pre-select the range of cells.
Revised to run to end of data in Column A. Sub EnterEightRows() Dim rng As Range Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)) x = 0 For Each cell In rng x = x + 1 If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert Next cell End Sub Gord Dibben MS Excel MVP On Tue, 22 Dec 2009 07:58:04 -0800, Deb wrote: Thank you, Erin. This works fantastically. I can't thank you enough. It will save me so much time when before I was doing this step manually. Just curious, will this macro work for whatever length of data that I may have, just run continuous until there is no more data to manipulate? "Erin Searfoss" wrote: This will work too. Select the cells in the first column of the range where you want to insert the rows. Sub EnterEightRows() x = 0 For Each cell In Selection.Cells x = x + 1 If (x + 7) Mod 38 = 0 Then cell.Resize(8, 1).EntireRow.Insert Next cell End Sub "Deb" wrote: To be exact, I want to be able to insert 8 blank rows after Row 30, Row 68, Row 106, Row 144, Row 182, and Row 220. Is there a macro to set up for that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting blank rows for missing data in order to transpose | Excel Worksheet Functions | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
Search numerous spreadsheets for specific data. | Excel Worksheet Functions | |||
How do I remove numerous blank rows from Excel spreadsheet? | Excel Discussion (Misc queries) | |||
inserting specific # of rows | Excel Discussion (Misc queries) |