Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding rows to spreadsheet
How can I automatically enter a blank row every 6 rows in excel. doing it
manually is too time consuming because the spreadsheet is so large. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding rows to spreadsheet
sub addblankrowevery6()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6 Rows(i).Insert Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "janets" wrote in message ... How can I automatically enter a blank row every 6 rows in excel. doing it manually is too time consuming because the spreadsheet is so large. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding rows to spreadsheet
Hi,
1. You can do this manually as follows in an empty column enter the formula =1/MOD(ROW(),6) and copy it down as far as necessary. The rows with DIV/0 will be the ones that Excel will Insert, so if they are on the wrong 6th row modify the formula by subtracting for example =1/(MOD(ROW()-1,6)) will put the DIV/0 on rows 1, 7, ... rather than 6, 12, 18... 2. Select the cells an copy and then choose Edit, Paste Special, Values 3. Press F5, Special, Constants and uncheck all but Errors, click OK 4. Press Ctrl + (Ctrl and the Plus key) and respond Entire Row. 5. Select the column where the formulas are and clear it. The VBA code to do this is given below. To run this select a blank column range, such as B1:B1000 and run the macro. This macro will run about 50-100 times faster than a standard For Loop or Do Loop macro. Sub InsertRows() Selection = "=1/MOD(ROW(),6)" Selection = Selection.Value Selection.SpecialCells(xlCellTypeConstants, 16).EntireRow.Insert Selection.EntireColumn.Delete End Sub If this helps, please click the Yes button. -- Thanks, Shane Devenshire "janets" wrote: How can I automatically enter a blank row every 6 rows in excel. doing it manually is too time consuming because the spreadsheet is so large. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding rows to spreadsheet
Don:
Thanks for taking the time to answer, but where do I enter this code? and do I need to tell the subroutine how large the spreadsheet is so that it knows when to stop? "Don Guillett" wrote: sub addblankrowevery6() For i = Cells(Rows.Count, "a").End(xlUp).Row To 5 Step -6 Rows(i).Insert Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "janets" wrote in message ... How can I automatically enter a blank row every 6 rows in excel. doing it manually is too time consuming because the spreadsheet is so large. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding new rows to an Excel spreadsheet | Charts and Charting in Excel | |||
Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
Sum only adding rows on page instead of entire spreadsheet - help | Excel Discussion (Misc queries) | |||
adding rows to spreadsheet after macro is written | Excel Programming | |||
Adding new rows to spreadsheet | Excel Programming |