Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Columns, Then deleting old columns | Excel Discussion (Misc queries) | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
Deleting row unless certain values in certain columns | Excel Programming | |||
Deleting Columns | Excel Programming | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions |