Automatically Inserting Blank Rows

[Pibsandsquibs]

Hello

As most spreadsheets I have a long list of data, some of the data in column A repeats itself for a mixture of 1, 2 or 3 rows then it has a new number that again could repeat itself for 1,2 or 3 rows.

Is there anyway to make my spreadsheet clearer that I can insert a blank line automatically where the number in column A changes.

Current Example

120820
120820
120821
120823
120823
120823

Thanks

 

[hagia_sofia]

Hello, something like this (where A1:C6 represents your dataset and A1:A6 are those numbers)?

=LET(
data,A1:C6,
category,A1:A6,
DROP(DROP(REDUCE("",UNIQUE(category),LAMBDA(x,y,IFNA(VSTACK(x,FILTER(data,category=y),""),""))),1),-1))

 

[Skyybot]

Paste this code into the Sheet object of your VBA Project.

Option Explicit

Sub InsertBlank()
Dim rng As Range, cell As Range
Dim i As Integer
Set rng = Range(Cells(1, 1), Cells(Sheet1.UsedRange.Rows.Count, 1))
For i = rng.Rows.Count To 1 Step -1
    If i = 1 Then Exit Sub
    If Not rng.Cells(i, 1).Value = rng.Cells(i - 1, 1).Value Then
        rng.Cells(i, 1).EntireRow.Insert
    End If
Next i
End Sub

 

[kvsrinivasamurthy]

Try.

Dim Lr&, T&, S$
Dim M
Lr = Range("A" & Rows.Count).End(xlUp).Row
M = Filter(Evaluate("Transpose(If(A2:A" & Lr - 1 & "<>A3:A" & Lr & ",""A""&Row(A3:A" & Lr & "),false))"), False, False)
For T = UBound(M) To 0 Step -1
S = S & "," & M(T)
If Len(S) > 240 Or T = 0 Then Range(Mid(S, 2)).EntireRow.Insert: S = ""
Next T
End Sub