Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert blank rows repeatedly between every data row in Excel
Could you guide me please....
I need to insert 5 blank rows repeatedly between every existing data rows for approximately 300 rows. If I go about doing the repeat short-cut "Control+Y", it just repeats inserting ONE row only between the consecutive data row. Is there some command, which helps me highlight all the rows & allows me to insert 5 blank rows between every consecutive existing data row? Thanks in advance for your kind advice. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert blank rows repeatedly between every data row in Excel
Hi Being very new to excel coding, this code might be lengthy, but it works. Public Sub insert_row() Const TestColumn As Long = 1 'have taken column A as 1, thus B would be 2' Dim cRows As Long Dim i As Long cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row For i = cRows To 2 Step -1 If Cells(i, TestColumn).Value < IsNotBlank Then Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert End If Next i End Sub Hope this suffice your cause. SAS -- abcdexcel ------------------------------------------------------------------------ abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021 View this thread: http://www.excelforum.com/showthread...hreadid=499280 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert blank rows repeatedly between every data row in Excel
The 7 lines of code you have between the For Line and the Next line can be
replaced with the following 3 lines of code: Cells(i, TestColumn).Select ActiveCell.Rows("1:4").EntireRow.Insert ActiveCell.Offset(-1, 0).Select ''' Just one alternative,,, FWIW... "abcdexcel" wrote in message ... Hi Being very new to excel coding, this code might be lengthy, but it works. Public Sub insert_row() Const TestColumn As Long = 1 'have taken column A as 1, thus B would be 2' Dim cRows As Long Dim i As Long cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row For i = cRows To 2 Step -1 If Cells(i, TestColumn).Value < IsNotBlank Then Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert Cells(i, TestColumn).EntireRow.Insert End If Next i End Sub Hope this suffice your cause. SAS -- abcdexcel ------------------------------------------------------------------------ abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021 View this thread: http://www.excelforum.com/showthread...hreadid=499280 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Microsoft query data into excel changes linked rows | Links and Linking in Excel | |||
EXCEL HELP (ROWS & DATA) | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Activate a macro to insert a row and copy the formuals from the rows above to the blank row | Excel Discussion (Misc queries) |