Entire Row Delete VBA

AndrewGKenneth

Board Regular
Joined
Aug 6, 2018
Messages
59
Hi There,

I am having an issue with some code I have created. What I would like to achieve is on my worksheet "Drawings" if the value in Column B is the same as Column F then the entire row will be deleted automatically.

Here is the code I currently have:

Dim rng As Range, DelRng As Range
Dim LastRow As Long
Dim Value As String

On Error Resume Next
If Not Sheets("Drawings").Range("B2").Value = "" Then
Lrow = Sheets("Drawings").Range("$B$2:B2")
For Each rng in DelRng
If rng.Value = Sheets("Drawings").Range("F2").Value then
Rng.EntireRow.Delete
End If
Next
End If
End Sub

The problem is with my current code it is only deleting the first row not scanning through the entire sheet and deleting any rows where B and F have the same value.

Could someone please assist me? Thanks in advance!

Kind Regards,
Andrew
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
When deleting is is best to work from bottom to top.

Code:
Dim i As Long

With ThisWorkbook.Sheets("Drawing").Range("B:B")
    With Range(.Cells(.Rows.Count,1).End(xlup), .Cells(2,1))
        For i = .Rows.Count to 1 Step -1
            With .Cells(i,1).EntireRow
                If .Range("B1").Value = .Range("F1").Value Then
                    .Delete shift:=xlUp
                End If
            End With
        Next i
    End With
End With
 

AndrewGKenneth

Board Regular
Joined
Aug 6, 2018
Messages
59
Thanks so much Mike, I will give that a go when I am back in the office on monday and let you know how it goes.

All the best,
Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top