Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting A Row Where Data Changes
I am very new to VBA so any help would much appreciated.
I would like to know how to write a macro that inserts an entire row of cells everytime the the data in cell b is different from the cell above it. It would need to be a continuous process until there was no more data. eg. A B 10 40 12 40 < Insert a row between the 40 and 60 17 60 22 60 < Insert a row between the 60 and 70 24 70 29 70 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 The above was some code that I came up with but I am pulling my hair out trying to make it work Thanks for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting A Row Where Data Changes
Hi
This sample code will insert a blank row every time value entered in column B is different from value one row above. Right-click on your worksheet's tab, choose View code and paste the following exemple : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 2 Or Target.Row = 1 Then Exit Sub If Target.Offset(-1, 0).Value < Target.Value Then Target.EntireRow.Insert End If End Sub HTH Cordially Pascal "TP" a écrit dans le message de om... I am very new to VBA so any help would much appreciated. I would like to know how to write a macro that inserts an entire row of cells everytime the the data in cell b is different from the cell above it. It would need to be a continuous process until there was no more data. eg. A B 10 40 12 40 < Insert a row between the 40 and 60 17 60 22 60 < Insert a row between the 60 and 70 24 70 29 70 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 The above was some code that I came up with but I am pulling my hair out trying to make it work Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a row and filling in data | Excel Discussion (Misc queries) | |||
Inserting data question | Excel Discussion (Misc queries) | |||
locate data from a chart by inserting data into a cell | Excel Worksheet Functions | |||
Inserting Data | Excel Discussion (Misc queries) | |||
Inserting row of data | Excel Programming |