[Help] Macro code to insert rows in between specific cells with respect to its other cell value

Question

Hi,

I'm trying to find a macro code that inserts rows in between specific cells w.r.t its corresponding other cell values. Please follow the example to understand my issue in excel.

The first image is the raw data. Please take a look at " Column J " initially.

It has vehicle tracking and has certain ID's from 1 to 3. I need to insert an empty row before every vehicle tracking ID. i.e, from column J, just before the value 1 in row-2, an empty row is needed. All this is applicable to all its corresponding rows, BUT except the Column C and Column D. values in these two columns remain in the same position, and all other values will be moved below. Similarly, just before the value 2 in column J, the above procedures would need to happen. And again just before the value 3 in column J

Please take a look at the second image for the output sample that I'm willing to get.

Please help me to figure it out, since i need to do this for a bulk data. So, its my immense pleasure to get Macro code/ formula/conditional formatting in this reply.

Thank you.

Answer 1

Sub InsertCells() Dim r As Long Dim m As Long Application.Cursor = xlWait Application.ScreenUpdating = False m = Range("J" & Rows.Count).End(xlUp).Row For r = m To 2 Step -1 If Range("J" & r).Value <> Range("J" & r - 1).Value Then Range("A" & r).Resize(1, 10).Insert Shift:=xlShiftDown End If Next r Range("C2:D2").Delete Shift:=xlShiftUp Application.ScreenUpdating = True Application.Cursor = xlDefault End Sub