Autofill Filtered Dynamic Range using VBA

[Alvin187]

Hi Team,
I am very new to VBA so please be gentle.

I am trying to automate a lengthy process that I do everyday and I have recorded a macro to help me do the same.
One part of the process involves filtering a set of names and replacing said names with one common term.
the code - given below - works but it turns every row - not considering its filtered or not - into the term.

Code.

' "Filing in progress" applied to all relavent cells
    Range("I2").Select
    ActiveSheet.Range("$A$1:$V$89100").AutoFilter Field:=9, Criteria1:=Array( _
        "name 1", "name 2", "name 3", "name 4", "name 5", "name 6"), Operator:=xlFilterValues
    
    Range("H1").Select
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 9).Select
    ActiveCell.Value = "Filing in Progress"
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LastRow, ActiveCell.Column))

I want to add/use .SpecialCells(xlCellTypeVisible) but dont know where to do the same.

points to note:
1) As its a filtered column there is no guarantee that the first cell after heading would be I2
2) As its filtered there is no guarantee regarding the size of the column. it will be less than the length of column
3) I know usage of AcitveSheet & Cell slows down the macro, but frankly I dont know how to change these

Pls help, thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

   Dim UsdRws As Long
   
   With ActiveSheet
      UsdRws = Range("I" & Rows.Count).End(xlUp).Row
      .Range("A1:V" & UsdRws).AutoFilter Field:=9, Criteria1:=Array( _
         "name 1", "name 2", "name 3", "name 4", "name 5", "name 6"), Operator:=xlFilterValues
      If .Range("I1:I" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(9).Value = "Filing in Progress"
      End If
    End With

 

[Alvin187]

Hi & welcome to MrExcel.
How about

   Dim UsdRws As Long
  
   With ActiveSheet
      UsdRws = Range("I" & Rows.Count).End(xlUp).Row
      .Range("A1:V" & UsdRws).AutoFilter Field:=9, Criteria1:=Array( _
         "name 1", "name 2", "name 3", "name 4", "name 5", "name 6"), Operator:=xlFilterValues
      If .Range("I1:I" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(9).Value = "Filing in Progress"
      End If
    End With

Hi Fluff,

Thank you so much.. this code works like a dream..

Just 2 questions!! I didnt understand it that's why

1) What does this code do?
UsdRws = Range("I" & Rows.Count).End(xlUp).Row
.Range("A1:V" & UsdRws).AutoFilter Field:=9,

2) What does this code do?
If .Range("I1:I" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(9).Value = "Filing in Progress"

 

[Fluff]

This line UsdRws = Range("I" & Rows.Count).End(xlUp).Row calculates the last used row in col I
and this line If .Range("I1:I" & UsdRws).SpecialCells(xlVisible).Count > 1 Then checks to see if there are any visible rows in the filtered data.