Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Entire Rows
Hi,
Would somebody be able to help me? I have a excel spreadsheet and wanted a macro that would insert an entire row everytime the number above it in column C was different. eg. A B C 1 10 1 20 2 11 3 20 3 12 21 30 < Insert Row 4 13 27 30 5 14 11 40 < Insert Row So far this is the only code i can write but it wont work: Dim i As Integer i = 2 While Range("C" & i) < "" Do If Range("C" & i) < Range("C" & i + 1) Then Rows(i & ":" & i + 1).Insert Shift:=xlDown i = i + 1 End If i = i + 1 Loop Wend End Sub Would anybody be able to assist me? TP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Entire Rows
When you insert or delete rows it is best to
start from the bottom using STEP -1 so that you don't trip all over yourself with changing rows. Modified without testing from example Insert Blank Rows on Column A change of value (#ColAchg) http://www.mvps.org/dmcritchie/excel...ow.htm#colAchg Sub InsertRow_C_Chg() Dim irow As Long, vcurrent As String, i As Long '// find last used cell in Column A irow = Cells(Rows.Count, "C").End(xlUp).Row '// get value of that cell in Column C (column 3) vcurrent = Cells(irow, 3).Value ' Col C '// rows are inserted by looping from bottom For i = irow To 2 Step -1 If Cells(i, 3).Value < vcurrent Then 'Col C vcurrent = Cells(i, 3).Value 'Col C Rows(i + 1).Insert End If Next i End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "TP" wrote in message om... Hi, Would somebody be able to help me? I have a excel spreadsheet and wanted a macro that would insert an entire row everytime the number above it in column C was different. eg. A B C 1 10 1 20 2 11 3 20 3 12 21 30 < Insert Row 4 13 27 30 5 14 11 40 < Insert Row So far this is the only code i can write but it wont work: Dim i As Integer i = 2 While Range("C" & i) < "" Do If Range("C" & i) < Range("C" & i + 1) Then Rows(i & ":" & i + 1).Insert Shift:=xlDown i = i + 1 End If i = i + 1 Loop Wend End Sub Would anybody be able to assist me? TP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting rows inbetween rows of data ? | Excel Discussion (Misc queries) | |||
Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
inserting rows in entire sheet | Excel Discussion (Misc queries) | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
Deleting entire rows | Excel Programming |