Visual Basic Excel - Macro to delete row

Question

I have 2 sheets (with phone numbers) in my document. if the number exists in sheet1 i want to remove the row from sheet 2.

I'm almost there (This is the first time I am using VBA). But could anyone help me with the last part.

Sub CleanList()

    Dim stopList As Range, cell1 As Range

    Set stopList = Sheet1.Range("A1:A10000")

    For Each cell1 In stopList
        Dim fullList As Range, cell2 As Range
        Set fullList = Sheet2.Range("A2:A10000")

        For Each cell2 In fullList
            If NumberFix(cell1.Value) = NumberFix(cell2.Value) Then
                cell2.EntireRow.Delete
            End If
        Next cell2
    Next cell1

End Sub

Private Function NumberFix(ByVal nr As String) As String

    If Not nr.StartsWith("46") Then
        nr = "46" + nr
    End If

    NumberFix = nr

End Function

Which version of Excel are you using? And could you clarify the bit about "need help with the last part"? You may want to have a look at ozgrid.com/VBA/RemoveDuplicates.htm which is one of many solutions to remove duplicates from ranges. — ExternalUse
– ExternalUse, Commented Apr 25, 2012 at 12:35
+ 1 @ExternalUse: Yup Advanced filter is one of the fastest way to remove duplicates — Siddharth Rout
– Siddharth Rout, Commented Apr 25, 2012 at 14:13

Brad · Accepted Answer · 2012-04-25 12:51:08Z

The first thing is the way your using nr.StartsWith is more VB.NET-esque. THe function your looking for in VBA (probably not VB-script btw) is

Dim firstTwoChar As String
firstTwoChar = Mid(nr, 1, 2)

If Not firstTwoChar = "46" Then
    nr = "46" + nr
End If

NumberFix = nr

But even with that I'd say you should not be using a for...each iterator if you are deleting rows. The problem is when you delete row 5 then row 6 becomes row 5 and the next row you go to is row "6" but is actually row 7 in the original list, effectively skipping the original row 6.

You need to move backwards. Something like

Sub CleanList()

    Dim stopList As Range, cell1 As Range

    Set stopList = Sheet1.Range("A1:A10000")

    For Each cell1 In stopList
        Dim fullList As Range, cell2 As Range

        Dim firstRowSheet2 As Integer, lastRowSheet2 As Integer, r As Integer
        Dim sheet1sNumber As String
        sheet1sNumber = NumberFix(cell1.Value)   'you really only need to do this once 
                                                 so you may as well bring it out of
                                                 the for loop and store the value and 
                                                 not recalculate each time
        Dim cell2 As Range
        For r = firstRowSheet2 To lastRowSheet2 Step -1 
                         '"Step -1" allows you to move backwards through the loop
            With Sheet2
                Set cell2 = .Cells(r, 1)
                If sheet1sNumber = NumberFix(cell2.Value) Then
                        cell2.EntireRow.Delete
                    End If
            End With

        Next r

    Next cell1

End Sub

But of course @ExternalUse is right. There are a lot of built in options for removing duplicates from a list. Unless you are trying to learn VBA, then this is a good exercise.

Thanks alot Brad. I added Start and End values for the for-loop. Only thing that were missing. Works like a charm now.

Collectives™ on Stack Overflow

Visual Basic Excel - Macro to delete row

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related