delete entire row based on conditional test of column A
 

I have a spreadsheet with various invoice numbers in column A:
For example:
FC110107-002
PB110107-001
LV010707-001

I need a VBA macro that will delete delete an entire row if the LEFT 2
characters of a cell in column A have either: FC, PB, or GR

The default range of my data is usually rows 2 - 2000

I cannot seem to find other snippets of code that work for my needs, so any
help would be appreciated.

delete entire row based on conditional test of column A
 

Try something like the following:

Sub AAA()

Dim LastRow As Long
Dim FirstRow As Long
Dim RowNdx As Long
Dim WS As Worksheet

FirstRow = 1 '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED

With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To FirstRow Step -1
Select Case Left(.Cells(RowNdx, "A").Value, 2)
Case "FC", "PB", "GR"
.Rows(RowNdx).Delete shift:=xlUp
Case Else
' do nothing
End Select
Next RowNdx
End With

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"guillermo.ht" wrote in message
...
I have a spreadsheet with various invoice numbers in column A:
For example:
FC110107-002
PB110107-001
LV010707-001

I need a VBA macro that will delete delete an entire row if the LEFT 2
characters of a cell in column A have either: FC, PB, or GR

The default range of my data is usually rows 2 - 2000

I cannot seem to find other snippets of code that work for my needs, so
any
help would be appreciated.

delete entire row based on conditional test of column A
 

Thank you very much. This worked perfectly and has saved me a serious headache.

"Chip Pearson" wrote:

Try something like the following:

Sub AAA()

Dim LastRow As Long
Dim FirstRow As Long
Dim RowNdx As Long
Dim WS As Worksheet

FirstRow = 1 '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED

With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To FirstRow Step -1
Select Case Left(.Cells(RowNdx, "A").Value, 2)
Case "FC", "PB", "GR"
.Rows(RowNdx).Delete shift:=xlUp
Case Else
' do nothing
End Select
Next RowNdx
End With

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"guillermo.ht" wrote in message
...
I have a spreadsheet with various invoice numbers in column A:
For example:
FC110107-002
PB110107-001
LV010707-001

I need a VBA macro that will delete delete an entire row if the LEFT 2
characters of a cell in column A have either: FC, PB, or GR

The default range of my data is usually rows 2 - 2000

I cannot seem to find other snippets of code that work for my needs, so
any
help would be appreciated.