CODEWITHSUNDEEP
vbaexcel
A Cohen
A Cohen

Reputation: 456

VBA - A shorter If/Or Statement

I have been looking for a shorter way to to use this If/Or statement, but to no avail. 

Sheets("Raw").Select
Dim rowCount As Integer, rows As Integer
rows = 0
Dim CRC As String

rowCount = Range("I2", Range("I2").End(xlDown)).rows.Count
For rows = 1 To rowCount
    Range("I1").Offset(rows, 0).Select
    CRC = ActiveCell.Value
    If CRC = "Admin Cancellation" _
    Or CRC = "Building sold / Management Co changed" _
    Or CRC = "Contract Renewal delayed" _
    Or CRC = "System decommissioned" _
    Or CRC = "Building demolished" _
    Or CRC = "cancelled due to credit hold/risk" Then
        ActiveCell.rows.EntireRow.Select
        Selection.Delete Shift:=xlUp
        rows = rows - 1
        rowCount = rowCount - 1
    End If
Next rows

Thanks in advance!

Upvotes: 1

Views: 171

Answers (2)

user4039065
user4039065

Reputation:

Try a Select... Case statement.

Dim rws As Long, rcnt As Long, crc As String

With Worksheets("Raw")
    rcnt = .Cells(.Rows.Count, "I").End(xlUp).Row
    For rws = rcnt To 2 Step -1
        Select Case LCase(.Cells(rws, "I").Value2)
            Case "admin cancellation", "building sold / management co changed", _
                 "contract renewal delayed", "system decommissioned", _
                 "building demolished", "cancelled due to credit hold/risk"
                 .Rows(rws).EntireRow.Delete
        End Select
    Next rws
End With

You should always loop from bottom to top when deleting rows or you risk skipping over one when you delete and iterate to the next row. It is 'bad practice' to reuse reserved words as variable names. It is also considered 'bad practice' to change the value of the iteration var in a For .... Next loop.

Upvotes: 3

braX
braX

Reputation: 11755

  Select Case CRC
    Case "Admin Cancellation", "Building sold / Management Co changed", _
         "Contract Renewal delayed", "System decommissioned", "Building demolished", _
         "cancelled due to credit hold/risk"
      ' do something
  End Select

Upvotes: 2

Related Questions