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

[avicric]

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.

 

[avicric]

then why did you say

I did say that but I had to use my tweak as with what you suggested....it gave an error right at the 3 line for filter....

 

[Fluff]

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]

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]

Is that with your code, or my original code?

 

[avicric]

Is that with your code, or my original code?

yes sir.... it is....

 

[Fluff]

Then what was wrong with the code I originally provided?

 

[avicric]

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]

(removed as I misread)

 

[Fluff]

Which line of code & what was the error?

 

[avicric]

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