My macro does not run completely

Question

My macro does not run completely

Navigation

#1 by (1 votes)

0

I'm doing a macro that consists of deleting duplicate data from a column in excel, (keep the first data and delete the remaining data), the problem is that I have to execute the macro many times to perform the task completely, this is my code, to see if you can guide me.

Sub Principal()
    Dim lngFila As Long
    Dim strB As String
    Dim strColumnaB As String
    Dim valor1 As String
    Dim valor2 As String
    Dim contador As Integer

    lngFila = 1
    strB = "B"
    strColumnaB = strB + CStr(lngFila)

    Do
        Range(strColumnaB).Select
        valor1 = Range(strColumnaB).Value

        lngFila = lngFila + 1
        strColumnaB = strB + CStr(lngFila)
        Range(strColumnaB).Select
        valor2 = Range(strColumnaB).Value

        If (valor1 = valor2) Then
            Range(strColumnaB).EntireRow.Delete
        End If

    Loop Until Range(strColumnaB).Value = ""

End Sub

macros excel

asked by Silvestre Silva 29.09.2017 в 05:52

source

1 answer

best process and loop in Ruby button upload image to folder and permissions 755 [closed]

score 1 · Accepted Answer

Indeed, because your macro what it does is go through the column and delete one row advances another, which, when you have more than one repeated will skip any of the repetitions. What you should do is if a fila repeats with fila + 1 , effectively erase fila + 1 , but do not move forward, with this you eliminate all the repetitions and you just advance when fila and fila + 1 are different.

I also suggest some changes to make the code more compact:

Sub DeleteRepeatedSecuentialValues()

    Dim Row As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    Row = 1
    While ws.Cells(Row, 2).Value <> ""
        If ws.Cells(Row, 2).Value = ws.Cells(Row + 1, 2).Value Then
            Range("B" & (Row + 1)).EntireRow.Delete
        Else
            Row = Row + 1
        End If
    Wend

End Sub

Instead of selecting cell ranges to verify values, we access directly by index each cell in column 2 (B).