VBA. Delete row in a range if Values in Column A do not equal Cell B2

MsPretzel

New Member
Joined
Jun 14, 2018
Messages
5
I am very rusty on VBA and need some help!
I need to delete rows within a range (rows 6 to 85) if the value in Column A does not equal B2.
I have pasted some example data below

ABC
1CurrencyGBP
2EmployeeCharlie
3
4EmployeeEvent nameValue
5AbiLondon2
6BeckySingapore4
7CharlieParis3
8DaveLondon4
9
EdSingapore3
10CharlieParis6
11DaveLondon3
12EdSingapore6
13CharlieParis4
14AbiLondon3

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You could try something like...

Code:
Sub YourRoutine()
Dim Cnt As Integer


For Cnt = 85 To 6 Step -1
    If ActiveSheet.Range("A" & Cnt).Value <> ActiveSheet.Range("B2") Then
        ActiveSheet.Range(Cnt & ":" & Cnt).EntireRow.Delete
    End If
Next Cnt


End Sub
 
Upvote 0
Apologies, i may have been unclear about my second post... try using:

Code:
Sub YourRoutine()
Dim Cnt As Integer


For Cnt = 85 To 6 Step -1
     If ActiveSheet.Range("A" & Cnt).Value <> ActiveSheet.Range("B2").Value Then
          ActiveSheet.Rows(Cnt).EntireRow.Delete
     End If
Next Cnt


End Sub
 
Last edited:
Upvote 0
Another option
Code:
Sub Delrws()
   With Range("A6:A85")
      .Value = Evaluate(Replace("if(@<>$B$2,""=XXX"",@)", "@", .Address))
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
End Sub
 
Upvote 0
Apologies, i may have been unclear about my second post... try using:

Code:
Sub YourRoutine()
Dim Cnt As Integer


For Cnt = 85 To 6 Step -1
     If ActiveSheet.Range("A" & Cnt).Value <> ActiveSheet.Range("B2").Value Then
          ActiveSheet.Rows(Cnt).EntireRow.Delete
     End If
Next Cnt


End Sub

Thanks, I used the following and it deleted all of the data up to row 6 - I think there must be something in the range line which I'm missing. I feel like it's really obvious and I just can't spot it! Should I be using xlup?

Sub DelUnreqRows()
Dim LRow As Integer


For LRow = 85 to 6 Step -1
If ActiveSheet.Range("A" & LRow).Value <> ActiveSheet.Range("B2") Then
ActiveSheet.Rows(LRow).EntireRow.Delete
End If
Next LRow


End Sub
 
Upvote 0
How odd, it worked for me. Is the string in cell B2 the same as some of the strings in column A (i'm just wondering if B2 has a space after the name "Charlie" or something similar)? Fluff can hopefully point us both in the right direction...
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top