Nested if statements or something else?

thommo41

Board Regular
Joined
Nov 10, 2006
Messages
142
Hi,

I have a routine that deletes rows when the ammount of characters in cell "C & row" is less than 9 characters. This works fine.

I now have to throw in a condition to this rule, whereby the row only gets deleted if the prefix of the cell "C & row" matches a certain character. If it does match that character then the check for less than 9 characters needs to run and delete the row accoringly. I hope this makes sense to somebody? The code below may help...

Code:
Sub delrow()
Dim rcount, rloop
' Deletes the rows where the Field code is blank, or less than 3 characters
rcount = Sheets("sheet1").UsedRange.Rows.Count
For rloop = rcount To 2 Step -1
    If Left(Range("C" & rloop).Value, 2) = "BP" Then
        'MsgBox ("anything")
        If Len(Range("C" & rloop).Value) < 9 Then
        MsgBox ("anything")
        Rows(rloop).Delete Shift:=xlUp
        End If
    End If
    If Left(Range("C" & rloop).Value, 2) = "ME" Then
        If Len(Range("C" & rloop).Value) < 9 Then
        Rows(rloop).Delete Shift:=xlUp
        End If
    End If
Next
End Sub

Basically, the "deleting row if cell (c & row) is less than 9 characters" only needs to happen if the prefix of cell (c & row) is ME or BP...

HELP!
Thanks in advance
Alan
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You can just stick it all in the first If statement with an Or thrown in for good measure!!
Code:
Dim rcount As Long, rloop As Long
' Deletes the rows where the Field code is blank, or less than 9 characters
rcount = Sheets("sheet1").UsedRange.Rows.Count
For rloop = rcount To 2 Step -1
    If Left(Range("C" & rloop).Value, 2) = "BP" Or Left(Range("C" & rloop).Value, 2) = "ME" Then
        If Len(Range("C" & rloop).Value) < 9 Then
            MsgBox ("anything")
            Rows(rloop).Delete Shift:=xlUp
        End If
    End If
Next
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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