Counter to insert blank line every 5 equal cells

Question

Counter to insert blank line every 5 equal cells

Navigation

#1 by (3 votes)

2

Hello, I'm trying to make a macro for a button, which puts a blank line every 5 cells with equal content, however I'm quite lazy in the VB syntax and I'm not sure if the logic is correct either, 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

excel visual-basic-6 excel-vba

asked by anonymous 05.04.2018 / 21:16

1 answer

Div does not respect image limit in css How to count the number of all instances of a class and their respective subclasses?

score 3 · Accepted Answer

You're close, I've 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