Counter to insert blank row every 5 equal cells

Question

Counter to insert blank row every 5 equal cells

Hello, I'm trying to make a macro for a button, which puts a blank line every 5 cells with equal content, but I'm quite lay in the syntax of VB and I'm not sure if the logic is correct too, example of what should happen:

ADA
ADA
ADA
ADA
ADA

ADA

My current code is this:

Private Sub Divide5_Click()
Dim Lst As Long
Dim n As Long
Dim i As Integer
Lst = Range("A" & Rows.Count).End(x1Up).Row
i = 0
For n = 2 To Lst Step 1
  With Range("A" & n)
    If .Value = .Offset(1).Value Then
     i = i + 1
    End If
    If i = 5 Then
     .EntireRow.Insert
    End If
  End With
Next n
End Sub

2

excel visual-basic-6 excel-vba

Author: Fernando Gross, 2018-04-05

Source

1 answers

score 3 · Accepted Answer

You're close, I made some modifications to get the interactions you want:

Sub Divide5_Click()
Dim Lst As Long
Dim n As Long
Dim i As Integer

Lst = Range("A" & Rows.Count).End(xlUp).Row
i = 0
n = 2
Do While n <= Lst 'For não itera dinamicamente
    With Range("A" & n)
        If .Value = .Offset(-1).Value Then
            i = i + 1
            If i = 4 Then
                .Offset(1).EntireRow.Insert
                i = 0 'Reinicie o contador após inserir linha
                Lst = Lst + 1 'limite dinâmico
            End If
        Else
            i = 0 'Reinicie o contador se a célula mudar de valor e não completar 5
        End If
    End With
    n = n + 1
Loop
End Sub