VBA - Replacing filtered data

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
65
Office Version
  1. 365
Platform
  1. 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"
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
UNTESTED
Shouldn't this line be changed to
VBA Code:
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
TO
VBA Code:
For Each cell In Columns(5).SpecialCells(xlCellTypeVisible)
cell.Replace What:="QST - FUEL", Replacement:="Gasoline", LookAt:=xlPart
Next
 
Upvote 0

Forum statistics

Threads
1,214,635
Messages
6,120,660
Members
448,975
Latest member
sweeberry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top