Using VBA to remove a row which contains a keyword

[dleach19]

All,

I have a workbook which I am using to track a large number of deals. Column G in the Tracking Report provides a "Deal Status". I was hoping someone could help me with the VBA code to do the following:

1) go to the last active row in Column G on the Tracking Report worksheet

2) Begin Looping through the rows and identifying if Column G has the Deal Status as "Dead"

3) If the Deal Status is "Dead" Cut the entire row and move it to the "Dead Deals" sheet

4) If the status is anything but "Dead" leave it alone and move on to the next one

Any help would be greatly appreciated!

 

[JoeMo]

Welcome to the board.

If there are no formulas in column G try this, there's no need to loop.

Sub RemoveDeadDeals()
Dim lR As Long
lR = Range("G" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("G1:G" & lR).Replace what:="Dead", replacement:="#N/A", MatchCase:=False
On Error Resume Next
Range("G1:G" & lR).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Welcome to the board.

If there are no formulas in column G try this, there's no need to loop.

Sub RemoveDeadDeals()
Dim lR As Long
lR = Range("G" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("G1:G" & lR).Replace what:="Dead", replacement:="#N/A", MatchCase:=False
On Error Resume Next
Range("G1:G" & lR).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

I do not think that does what the OP asked for... see item #3 in his list.

 

[wallabiee]

Hi,

If we assume that our Active sheet where form we look for the Dead deals is called "All Deals"

I would recommend to filter the raw data in column G, copy the rows to the "Dead Deals" sheet and then clear up the "All Deals" sheet (delete the rows).
Code:

Sub FilterDeadDeals()


Dim lastrow As Long
Dim lastrow2 As Long


'Filter the column "G" (number 7) with our criteria which is "Dead"
lastrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
Sheets("All Deals").Range("A1").AutoFilter field:=7, Criteria1:="Dead"

'Copy the filtered rows
Sheets("All Deals").Rows("2:" & lastrow).Copy


'Activate the new sheet
Sheets("Dead Deals").Activate

'Find the last used row in the new spreadsheet
lastrow2 = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row + 1

'Paste on the first free cell
ActiveSheet.Range("A" & lastrow2).PasteSpecial xlPasteAll

'Activate the "All Deals" sheet and delete the "Dead" deals rows
Sheets("All Deals").Activate
Sheets("All Deals").Rows("2:" & lastrow).EntireRow.Delete

'Display all data
Sheets("All Deals").ShowAllData


End Sub



Feel Free to change the "All Deals" Sheet naming to whatever suits your needs (or anything else).

Cheers.

 

[JoeMo]

I do not think that does what the OP asked for... see item #3 in his list.

Oops! Read the OP with too much haste. Looks as though Post #4 is on top of it.

 

[dleach19]

Wallabiee,

Thank you for the detailed response. When I run your code I get an error (Subscript out of range) on this line item:

'Filter the column "G" (number 7) with our criteria which is "Dead"
lastrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
Sheets("All Deals").Range("A1").AutoFilter field:=7, Criteria1:="Dead"

Any thoughts?

 

[JoeMo]

Try running this with the sheet containing your data to be cut active:

Sub CutDeadDeals()
'Assumes headers in row 1
Dim lR As Long, R As Range, n As Long
lR = Range("G" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Set R = Range("G1:G" & lR).CurrentRegion
R.AutoFilter field:=7, Criteria1:="Dead"
Set R = R.Rows("2:" & lR)
R.Copy
With Sheets("Dead Deals")
    lR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lR).PasteSpecial Paste:=xlPasteValues
End With
On Error Resume Next
R.Delete shift:=xlUp
Range("G1").AutoFilter
Application.ScreenUpdating = True
End Sub

 

[wallabiee]

Wallabiee,

Thank you for the detailed response. When I run your code I get an error (Subscript out of range) on this line item:

'Filter the column "G" (number 7) with our criteria which is "Dead"
lastrow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
Sheets("All Deals").Range("A1").AutoFilter field:=7, Criteria1:="Dead"

Any thoughts?

Hi,

Is your sheet with the same name like "All Deals"?

Cheers.

 

[dleach19]

This worked perfectly! Thank you for the help.

 

[JoeMo]

This worked perfectly! Thank you for the help.

What does "This" refer to?