Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to insert row automatically after certain number?
Hi,
I have a list that start from 1 to 2851 row, what I want to do is inserting a blank row after 77 items. Each 77 items should be separated by row. Thansk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to insert row automatically after certain number?
Something like this should get you going:
Sub InsertNewRows() Const firstRow = 1 ' change if needed Const groupSize = 77 ' change if desired 'identify a column where there is 'something in a cell in it in the 'last used row on the sheet Const baseColumn = "A" Dim lastRow As Long 'for improved performance Application.ScreenUpdating = False 'choose starting point Range(baseColumn & groupSize + firstRow).Select 'initialize lastRow value lastRow = Range(baseColumn & Rows.Count).End(xlUp).Row 'do the work Do Until ActiveCell.Row lastRow ActiveCell.EntireRow.Insert ' new row ActiveCell.Offset(groupSize + 1, 0).Activate 'redefine last row lastRow = Range(baseColumn & Rows.Count).End(xlUp).Row Loop End Sub To put the code into your workbook press [Alt]+[F11] to open the VB Editor. In there choose Insert | Module and copy and paste the code above into it. Make any changes to baseColumn and firstRow that need to be made. Close the VB Editor, choose the sheet with data on it and use Tools | Macro | Macros to select the macro and [Run] it. "ghost" wrote: Hi, I have a list that start from 1 to 2851 row, what I want to do is inserting a blank row after 77 items. Each 77 items should be separated by row. Thansk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically insert a row | Excel Worksheet Functions | |||
automatically insert row | Excel Discussion (Misc queries) | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) | |||
Insert date automatically | Excel Discussion (Misc queries) | |||
Can you automatically insert next sequential number in an excel fo | Excel Discussion (Misc queries) |