ScatmanKyle
Board Regular
- Joined
- Oct 26, 2015
- Messages
- 65
- Office Version
- 365
- Platform
- Windows
I have a data set where I need to break out tax, but need to make sure tax on fuel is not counted. My process is to filter column L for anything beginning with "QST - FUEL", then change the filtered values in column E to "Gasoline". The below has worked for me when there's more than one filtered row, but if there's only one result, the word "Gasoline" fills in all the way to the end of the sheet. Any suggestions on the below code?
'Finds the last row of data to use as reference
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Filter the Description field for anything beginning with "QST - FUEL"
Range("E1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$X$" & lngLastRow).AutoFilter Field:=12, Criteria1:=Array( _
"QST- FUEL" & "*"), Operator:= _
xlFilterValues
'Changes all QST on fuel charges so they're expensed as they cannot be claimed due to the QST ITR
ActiveSheet.Range("E1").Select
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Value = "Gasoline"
'Finds the last row of data to use as reference
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Filter the Description field for anything beginning with "QST - FUEL"
Range("E1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$X$" & lngLastRow).AutoFilter Field:=12, Criteria1:=Array( _
"QST- FUEL" & "*"), Operator:= _
xlFilterValues
'Changes all QST on fuel charges so they're expensed as they cannot be claimed due to the QST ITR
ActiveSheet.Range("E1").Select
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Value = "Gasoline"