New here and relatively new to programming with VBA. I have a worksheet with addresses and associated information including the state. I want to filter the worksheet by state and count the number of filtered rows, pasting that number of rows next to the state abbreviation in the adjoining column: StateIN RCount
I can get the code to autofilter and list all the states used in the worksheet, but am having trouble figuring out how to tell Excel that I want it to give me the number of filtered rows (minus the header row) for each state listed. I have tried several things, but this is essentially the code I am using:
'filter and copy individual state data from vqueue worksheet to state worksheet
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim lastRow As Long
Dim sht As String
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
'specify sheet name in which the data is stored
sht = "vqueue"
'change filter column in the following code
lastRow = Sheets(sht).Cells(Rows.Count, "J").End(xlUp).Row
Set rng = Sheets(sht).Range("A1:AC" & lastRow)
Sheets(sht).Range("J1:J" & lastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AD1"), Unique:=True
For Each x In Range([AD2], Cells(Rows.Count, "AD").End(xlUp))
With rng
.AutoFilter
.AutoFilter Field:=10, Criteria1:=x.Value
Range("AE" & x).Value = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.Paste
End With
Next x
I'm not entirely sure I even have it placed in the right location in the for/with loop. Currently, I am getting a 424 Object required error. Thanks in advance for any help!
Bookmarks