Filter list then change cell value in results

StuLux

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
679
Office Version
  1. 365
Platform
  1. Windows
I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

Code: 
ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
[COLOR=red]'After filtering I want to change the value in column Y for only the filtered entries[/COLOR]CODE]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Code: 
ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
Range("Y2:Y5000").SpecialCells(xlCellTypeVisible).Value = "Your value here"
 
Upvote 0
That works great thanks - initially it was changing some blank cells as well so I changed the range to ensure it only went to the last record using a variable ctRAG. I'm now having a problem when there is no valid record to be filtered - the code in red is my attempt to check whether there are any visible cells but this doesn't work - any suggestions?

Code: 
Sheets("CMT_RAGs").Select
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$V$186").AutoFilter Field:=2, Criteria1:=ProjPRN
[COLOR=red]If Range("V2:V" & ctRAG) Is Not Null Then[/COLOR]
Range("V2:V" & ctRAG).SpecialCells(xlCellTypeVisible).Value = "CONFIDENTIAL PROJECT"
End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Paste results of Filter Function in place, or in separate sheet
Replies
0
Views
84
bertible
B
C
  • Question
Finding any two values in each cell text in a column of cells with both values as one Textbox1 variable
2
Replies
10
Views
207
chazrab
C
S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
116
bobsan42
bobsan42
A
  • Question
Creating A Validation List From Unique Values in A Column (Filtered and unfiltered)
Replies
3
Views
152
RoryA
RoryA
E
  • Question
Macro to apply filter and move result to another worksheet
Replies
1
Views
160
mumps
mumps

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

Share this page

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