Hiker95 provided me with a code as noted below. I tweaked it some. What I'm looking for is if Column H contains any portion of the word "Resolve" to exclude that row.
Right now it's looking for "Resolve" and "NA". I want it to look for any variation of "Resolve" (e.g. "resolved, Resolve:, Resolving) and "NA".
Any help would be appreciated. Thank you.
Code:
Sub bailsmb()
' hiker95, 10/13/2015, ME894007
Dim we As Worksheet
Dim c As Range, lr As Long, nr As Long
Application.ScreenUpdating = False
Set we = Sheets("Exceptions")
Columns("A:M").ColumnWidth = 75
Range("A:M").WrapText = True
With Sheets("Third Party")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("H1:H" & lr)
If c <> "Resolved" And c <> "NA" Then
nr = we.Cells(we.Rows.Count, "A").End(xlUp).Row + 1
If nr = 2 And we.Range("A1") = "" Then nr = 1
we.Range("A" & nr).Resize(, 12).Value = .Range("A" & c.Row).Resize(, 12).Value
we.Range("l" & nr).Value = .Range("n" & c.Row).Value
End If
Next c
End With
With Sheets("Branch Operations")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("H1:H" & lr)
If c <> "Resolved" And c <> "NA" Then
nr = we.Cells(we.Rows.Count, "A").End(xlUp).Row + 1
If nr = 2 And we.Range("A1") = "" Then nr = 1
we.Range("A" & nr).Resize(, 12).Value = .Range("A" & c.Row).Resize(, 12).Value
End If
Next c
End With
With we
.Columns("A:L").AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub
Right now it's looking for "Resolve" and "NA". I want it to look for any variation of "Resolve" (e.g. "resolved, Resolve:, Resolving) and "NA".
Any help would be appreciated. Thank you.
Last edited: