Macro help (2007)

Topic

New Reply

G’day loungers!
I am trying to figure out how to code the following situation in VBA and am hitting a wall. If the value in column U is equal to 9900 or 9915 and the value in column Y is equal to “CB” or “MB” then I want to delete that row. Any suggestions?

Reply | Quote

Replies

Hi Greg

Try this piece of code:

Sub RemoveRows()
    Dim r As Long
    Dim n As Long
    
    r = Range("A" & Rows.Count).End(xlUp).Row
    
    For n = r To 1 Step -1
        
        If Cells(r, 21) = 9900 Or Cells(r, 21) = 9915 _
        And Cells(r, 25) = "CB" Or Cells(r, 25) = "MB" Then
            Cells(r, 1).EntireRow.Delete
        Else
        End If
    Next n
End Sub

Reply | Quote

Hi Jerry,

I have tried the code and it is not deleting any rows. I have attached a sample of the data and the macro as I typed it in. I do not see why it is not working. There are 10 rows in the sample data that meet the criteria for deletion.

Reply | Quote

Just a tiny confusion between r and n. Try

Sub RemoveRows()
Dim r As Long
Dim n As Long
r = Range(“A” & Rows.Count).End(xlUp).Row
For n = r To 1 Step -1
If (Cells(n, 21) = 9900 Or Cells(n, 21) = 9915) _
And (Cells(n, 25) = “CB” Or Cells(n, 25) = “MB”) Then
Cells(n, 1).EntireRow.Delete
End If
Next n
End Sub

Reply | Quote

Hans, Perfect as always! Thank you to both you and Jerry!

Reply | Quote

Whoops

Sorry, that teach me to send untested code, try:

Sub RemoveRows()
    Dim r As Long
    Dim m As Long
    Dim n As Long
    
    r = Range("A" & Rows.Count).End(xlUp).Row
    
    For n = r To 1 Step -1
        
        If Cells(n, 21) = 9900 Or Cells(n, 21) = 9915 _
        And Cells(n, 25) = "CB" Or Cells(n, 25) = "MB" Then
            Cells(n, 1).EntireRow.Delete
        Else
        End If
    Next n
End Sub

Reply | Quote

You also need parentheses around the … Or … pairs because And has precedence above Or.

Reply | Quote

thanks…

Reply | Quote

So regarding Hans post about the parantheses, is this the correct placement?

If (Cells(n,21) = 9900 or Cells(n,21) = 9915) _
And (Cells(n,25) = “CB” or Cells(n,25) = “MB”) Then
Cells(n,1).EntreRow.Delete

Reply | Quote

Yes, as per Hans post post 727,185

Reply | Quote

Correct

I was rushing my coding so bypassed that issue but it makes good logic to break it down in this way as the parenthesis set precedence similar to the old conundrum of:

What do the following equal
1) 2*3+1
2) 2*(3+1)
3) (2*3)+1

Same as

If ( A=1 or A=2) and (B=3 or B=4)

The code will initially check the values in the the lefthand bracket and then check the values in the righthand set. By breaking this down it allows the code to identify the correct precedence for checking values.

So yes you are correct

If (Cells(n,21) = 9900 or Cells(n,21) = 9915) _
And (Cells(n,25) = “CB” or Cells(n,25) = “MB”) Then
Cells(n,1).EntreRow.Delete

Reply | Quote