How to insert blank row when value gets changed

[ThrottleWorks]

Hi,

I have a data set of 15 columns and 4000 rows (approx.).
First column is for dates. Dates in this column (Column A) are sorted to oldest to newest.
I want to insert a blank row if value in column A gets changed.

For example, in original dataset values are as mentioned below.

Range(“A1:O1”) are headers.
Range(“A2:A5”) value = 01-03-2016
Range (“A6:A10”) value= 02-03-2016
Range (“A11:A20”) value= 03-03-2016 and so on

I want to insert a blank row after A5, A10 and A20

Can anyone please help me in this.

Apologies for not uploading sample file, please let me know if you need sample file, I will try to upload it.

 

[NARAYANK991]

Hi Sachin ,

See if the following links help :

http://stackoverflow.com/questions/...ally-insert-a-blank-row-after-a-group-of-data

https://www.extendoffice.com/documents/excel/1868-excel-insert-blank-row-when-value-changes.html

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 sir, thanks a lot for the help.
I am checking this and will revert with details.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @NARAYANK991 sir, I used the code given at
http://stackoverflow.com/questions/...ally-insert-a-blank-row-after-a-group-of-data

It is awesome ! Thanks a lot.

 

[YasserKhalil]

Try this

Sub InsertRow()
    Dim I As Long
    For I = 2 To 100
        If Cells(I, 1).Value <> Cells(I, 1).Offset(1, 0).Value Then
            Cells(I, 1).Offset(1, 0).EntireRow.Insert: I = I + 1
        End If
    Next I
End Sub

 

[ThrottleWorks]

H @YasserKhalil sorry for late reply. I am checking this and will revert with details. Have a nice weekend.

 

[Marc L]

Hi !​

Sub Demo1()
    Application.ScreenUpdating = False
For R& = Cells(1).CurrentRegion.Rows.Count - 4 To 6 Step -5
    Rows(R).Insert xlShiftDown
Next
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

Sub Demo2()
    Application.ScreenUpdating = False
For R& = Cells(1).CurrentRegion.Rows.Count To 3 Step -1
    If Cells(R, 1).Value2 <> Cells(R - 1, 1).Value2 Then Rows(R).Insert xlShiftDown
Next
    Application.ScreenUpdating = True
End Sub

 

[jindon]

For example, in original dataset values are as mentioned below.

Range(“A1:O1”) are headers.
Range(“A2:A5”) value = 01-03-2016
Range (“A6:A10”) value= 02-03-2016
Range (“A11:A20”) value= 03-03-2016 and so on

I want to insert a blank row after A5, A10 and A20

One way.

Sub test()
    Columns(1).Insert
    With Range("b3", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
        .Formula = "=if(b2<>b3,if(a2=1,""a"",1),"""")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(2, 1).EntireRow.Insert
        .SpecialCells(2, 2).EntireRow.Insert
        On Error GoTo 0
    End With
    Columns(1).Delete
End Sub

 

[ThrottleWorks]

Hi @Marc L sir, and @jindon thanks a lot for the help. I am checking this and will revert with details. Have a nice week ahead.