VBA Code : Based on filter selected need to make changes in column

avicric

New Member
Joined
Apr 24, 2017
Messages
32
Hi All,

Need your help with VBA code, I have to make changes to a column based on the filter selected in another column.

So I have to put/select filter in "column E" based on the filtered item need to rename all items in "column S"...

I have managed the below code but if 1 of the item is missing from the filter in "column E" the VBA just hangs....

Selection.AutoFilter
ActiveSheet.Range("$A:$V").AutoFilter field:=5, Criteria1:= _
"Assigned to Finance"
Range("S1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range("S1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "Finance"

Thanks for your help in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,075
Office Version
  1. 365
Platform
  1. Windows
it gave an error right at the 3 line for filter....
Then why didn't you say something? I cannot help you if you don't tell me anything.
Also by changing the code & not saying anything, I assume that you are using my code & it's therefore impossible to debug as we are talking about totally different things.

What error did you get & which line of code was highlighted.
 

avicric

New Member
Joined
Apr 24, 2017
Messages
32
Then why didn't you say something? I cannot help you if you don't tell me anything.
Also by changing the code & not saying anything, I assume that you are using my code & it's therefore impossible to debug as we are talking about totally different things.

What error did you get & which line of code was highlighted.
Hi Fluff,

The below line is highlighted no error....

If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,075
Office Version
  1. 365
Platform
  1. Windows
Is that with your code, or my original code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,075
Office Version
  1. 365
Platform
  1. Windows
Then what was wrong with the code I originally provided?
 

avicric

New Member
Joined
Apr 24, 2017
Messages
32

ADVERTISEMENT

Then what was wrong with the code I originally provided?
I used the your code it is always got stuck and highlighted this line in the code you had provided(no error message in vba).... I dont know what was wrong...
hence I tweaked the code with my version by looking and many post & google...

but the code worked with part of your code and part of my code....lol...

but got the runtime error "6" overload....

back to square 1....
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
(removed as I misread)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,075
Office Version
  1. 365
Platform
  1. Windows
Which line of code & what was the error?
 

avicric

New Member
Joined
Apr 24, 2017
Messages
32
Which line of code & what was the error?

please find the details below

Sub avicric()
Dim UsdRws As Long

With ActiveSheet
UsdRws = .Range("E" & Rows.Count).End(xlUp).Row
.Range("A1:V" & UsdRws).AutoFilter 5, "Assigned to Finance"
If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "Finance"
End If
End With
End Sub
 

Forum statistics

Threads
1,136,635
Messages
5,676,902
Members
419,657
Latest member
ExcelAl1

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
Top