Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    A B C
    1 Currency GBP
    2 Employee Charlie
    3
    4 Employee Event name Value
    5 Abi London 2
    6 Becky Singapore 4
    7 Charlie Paris 3
    8 Dave London 4
    9 Ed Singapore 3
    10 Charlie Paris 6
    11 Dave London 3
    12 Ed Singapore 6
    13 Charlie Paris 4
    14 Abi London 3

  2. #2
    Board Regular James_Latimer's Avatar
    Join Date
    Jan 2009
    Location
    Sheffield, UK
    Posts
    409
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    ..
    Last edited by James_Latimer; Jun 14th, 2018 at 11:05 AM.

  3. #3
    Board Regular James_Latimer's Avatar
    Join Date
    Jan 2009
    Location
    Sheffield, UK
    Posts
    409
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular James_Latimer's Avatar
    Join Date
    Jan 2009
    Location
    Sheffield, UK
    Posts
    409
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    this line of code is probably cleaner

    Code:
    ActiveSheet.Rows(Cnt).EntireRow.Delete

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    Board Regular James_Latimer's Avatar
    Join Date
    Jan 2009
    Location
    Sheffield, UK
    Posts
    409
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by James_Latimer; Jun 14th, 2018 at 11:34 AM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,998
    Post Thanks / Like
    Mentioned
    278 Post(s)
    Tagged
    22 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by James_Latimer View Post
    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

  9. #9
    Board Regular James_Latimer's Avatar
    Join Date
    Jan 2009
    Location
    Sheffield, UK
    Posts
    409
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

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

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    It worked! Thanks so much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •