Visual Basic Excel - Macro to Delete Row
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 use VBA). But can 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
source to share
The first is that you use nr.StartsWith
more VB.NET-esque. The function you are 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 would argue that you shouldn't use an iterator for...each
if you are deleting lines. The problem is that when you delete line 5, then line 6 becomes line 5, and the next line you go to is line "6", but is actually line 7 in the original list, effectively skipping the original line 6.
You need to move back. 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 many built-in options for removing duplicates from the list. If you are not trying to learn VBA then this is a good exercise.
source to share