Autofill Filtered Dynamic Range using VBA

Alvin187

New Member
Joined
Jul 8, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA 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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,265
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
   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
 
Solution

Alvin187

New Member
Joined
Jul 8, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,265
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,132
Messages
5,704,458
Members
421,351
Latest member
Sheogorath

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