Deleting row unless certain values in certain columns
 

I need to delete rows unless they have " Return To Tsr " (A space
before and after the phrase) in column O OR " Sales " (A space before
and after the word) in column Q.

I have had trouble getting this done right. What I am working with
currently is below.

I appreciate any help you can provide.

Thanks
Kris

Sub Day1TSR()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=15, Criteria1:=" Return To Tsr "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("QueryUpdate")
End Sub
Sub Day1Sales()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=17, Criteria1:=" Sales "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("Day1TSR")
End Sub

Deleting row unless certain values in certain columns
 

Try selecting only visible cells after the autofilter:
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.SpecialCells(xlCellTypeVisible).Copy
otherwise excel "helps" you by grabbing the hidden rows...
HTH

Die_Another_Day

Kris wrote:
I need to delete rows unless they have " Return To Tsr " (A space
before and after the phrase) in column O OR " Sales " (A space before
and after the word) in column Q.

I have had trouble getting this done right. What I am working with
currently is below.

I appreciate any help you can provide.

Thanks
Kris

Sub Day1TSR()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=15, Criteria1:=" Return To Tsr "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("QueryUpdate")
End Sub
Sub Day1Sales()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=17, Criteria1:=" Sales "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("Day1TSR")
End Sub

Deleting row unless certain values in certain columns
 

Hello Kris,
I hope I am not missing your point but I tried this:
Spreadsheet Data:
A B C D E F
1
2 Return to Tsr
3 Sales
4
5 Return to Tsr Sales
6
7
8
9 Sales
10 Return to Tsr

[Code Starts]

Sub deleteRows()
Dim checkCol1 As Integer
Dim checkCol2 As Integer
Dim lastRow As Integer
Dim thisRow As Integer
Dim cRow As Integer

checkCol1 = 4 ' Col E
checkCol2 = 5 ' Col F
lastRow = 10

Range("A1").Select
thisRow = 1
cRow = 1
Do While cRow <= lastRow
If (Selection.Offset(thisRow - 1, checkCol1).Value < " Return to Tsr ") _
And (Selection.Offset(thisRow - 1, checkCol2).Value < " Sales ") Then
Selection.Offset(thisRow - 1, 0).EntireRow.Delete
thisRow = thisRow - 1
End If
thisRow = thisRow + 1
cRow = cRow + 1
Loop
End Sub

[Code Ends]

The spreadsheet now:
A B C D E F
2 Return to Tsr
3 Sales
5 Return to Tsr Sales
9 Sales
10 Return to Tsr

Is this what you desired?

ChasAA


"Kris" wrote:

I need to delete rows unless they have " Return To Tsr " (A space
before and after the phrase) in column O OR " Sales " (A space before
and after the word) in column Q.

I have had trouble getting this done right. What I am working with
currently is below.

I appreciate any help you can provide.

Thanks
Kris

Sub Day1TSR()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=15, Criteria1:=" Return To Tsr "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("QueryUpdate")
End Sub
Sub Day1Sales()
Dim row As Long
row = FindLastRow
Sheets("Day 1").Select
Selection.AutoFilter Field:=17, Criteria1:=" Sales "
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working").Select
Worksheets("Working").Cells(row, 1).Select
ActiveSheet.Paste
Sheets("Day 1").Select
Range("A1").Activate
Selection.AutoFilter
Application.Run ("Day1TSR")
End Sub