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

[MsPretzel]

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

[TABLE="class: grid, width: 433"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Currency[/TD]
[TD]GBP[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee[/TD]
[TD]Charlie[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee[/TD]
[TD]Event name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Abi[/TD]
[TD]London[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Becky[/TD]
[TD]Singapore[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Charlie[/TD]
[TD]Paris[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Dave[/TD]
[TD]London[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Ed[/TD]
[TD]Singapore[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Charlie[/TD]
[TD]Paris[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Dave[/TD]
[TD]London[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Ed[/TD]
[TD]Singapore[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Charlie[/TD]
[TD]Paris[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Abi[/TD]
[TD]London[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[James_Latimer]

..

 

[James_Latimer]

You could try something like...

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

 

[James_Latimer]

this line of code is probably cleaner

ActiveSheet.Rows(Cnt).EntireRow.Delete

 

[MsPretzel]

Thanks so much for your help, but this seems to delete all of the rows.

 

[James_Latimer]

Apologies, i may have been unclear about my second post... try using:

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

 

[Fluff]

Another option

Sub Delrws()
   With Range("A6:A85")
      .Value = Evaluate(Replace("if(@<>$B$2,""=XXX"",@)", "@", .Address))
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
End Sub

 

[MsPretzel]

Apologies, i may have been unclear about my second post... try using:

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

 

[James_Latimer]

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...

 

[MsPretzel]

It worked! Thanks so much!