Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Inserter Macro
Hello All,
I have data in Col A starting from Row 1 in set of 10 Rows each in continous form... going upto 22000+ rows For Eg. 1 2 3 4 5 6 7 8 9 10 ...... 22,000 rows down I wish to insert a blank row after every 10th Row so that the data in Col A is separated by a blank row after every 10th row... For eg. 1 .... .... 10 <blank row 11 ... ... 20 <blank row and so on so forth. I got the following macro from the NG and I have modified LastRow To 10 Step -10... to suit my needs but I dont get the desired results..Would somebody help me. What am I doing wrong? I have very limited knowledge of VBA Sub RowInserter() Dim LastRow As Long Dim i As Integer LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LastRow To 10 Step -10 Cells(i, 1).EntireRow.Insert Next i End Sub Thanks in advance Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Inserter Macro
Rashid, try the following:
Dim cell As Range Set cell = Cells(11, 1) While Not IsEmpty(cell) cell.EntireRow.Insert Set cell = cell.Offset(10, 0) Wend Bob Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Rashid Khan" wrote in message ... Hello All, I have data in Col A starting from Row 1 in set of 10 Rows each in continous form... going upto 22000+ rows For Eg. 1 2 3 4 5 6 7 8 9 10 ..... 22,000 rows down I wish to insert a blank row after every 10th Row so that the data in Col A is separated by a blank row after every 10th row... For eg. 1 ... ... 10 <blank row 11 .. .. 20 <blank row and so on so forth. I got the following macro from the NG and I have modified LastRow To 10 Step -10... to suit my needs but I dont get the desired results..Would somebody help me. What am I doing wrong? I have very limited knowledge of VBA Sub RowInserter() Dim LastRow As Long Dim i As Integer LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LastRow To 10 Step -10 Cells(i, 1).EntireRow.Insert Next i End Sub Thanks in advance Rashid Khan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Row Inserter Macro
Hi Bob,
Voila. It works like a charm. Thanks for your time and help. Rashid "Bob Flanagan" wrote in message ... Rashid, try the following: Dim cell As Range Set cell = Cells(11, 1) While Not IsEmpty(cell) cell.EntireRow.Insert Set cell = cell.Offset(10, 0) Wend Bob Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Rashid Khan" wrote in message ... Hello All, I have data in Col A starting from Row 1 in set of 10 Rows each in continous form... going upto 22000+ rows For Eg. 1 2 3 4 5 6 7 8 9 10 ..... 22,000 rows down I wish to insert a blank row after every 10th Row so that the data in Col A is separated by a blank row after every 10th row... For eg. 1 ... ... 10 <blank row 11 .. .. 20 <blank row and so on so forth. I got the following macro from the NG and I have modified LastRow To 10 Step -10... to suit my needs but I dont get the desired results..Would somebody help me. What am I doing wrong? I have very limited knowledge of VBA Sub RowInserter() Dim LastRow As Long Dim i As Integer LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LastRow To 10 Step -10 Cells(i, 1).EntireRow.Insert Next i End Sub Thanks in advance Rashid Khan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |