+ Reply to Thread
Results 1 to 3 of 3

Get count of filtered rows and paste result in column

Hybrid View

  1. #1
    Registered User
    Join Date
    06-28-2021
    Location
    Colorado, United States
    MS-Off Ver
    Office 365
    Posts
    5

    Get count of filtered rows and paste result in column

    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!
    Last edited by cyoder; 06-30-2021 at 05:58 PM. Reason: adding code tags

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Get count of filtered rows and paste result in column

    Try this...

    '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 ws        As Worksheet
        
        'specify sheet name in which the data is stored
        Set ws = Sheets("vqueue")
        
        'change filter column in the following code
        lastRow = ws.Cells(Rows.Count, "J").End(xlUp).Row
        Set rng = ws.Range("A1:AC" & lastRow)
        
        ws.Range("J1:J" & lastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("AD1"), Unique:=True
        
        For Each x In ws.Range("AD2", ws.Cells(Rows.Count, "AD").End(xlUp))
            With rng
                .AutoFilter
                .AutoFilter Field:=10, Criteria1:=x.Value
                ws.Range("AE" & x.Row).Value = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
        
                .SpecialCells(xlCellTypeVisible).Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
                ActiveSheet.Range("A1").PasteSpecial xlPasteValues
            End With
        Next x
        Application.CutCopyMode = False
        rng.AutoFilter
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    06-28-2021
    Location
    Colorado, United States
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Get count of filtered rows and paste result in column

    That worked like a charm! Thank you so much! I was really pulling my hair out over it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy the filtered dates, do Formatting and paste it back to filtered column
    By Rajkumar_h in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2017, 10:53 AM
  2. Count the values in column and paste the result in another sheet
    By Mikz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2017, 10:33 AM
  3. Count and paste result into column
    By flattimuss in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2016, 06:26 AM
  4. [SOLVED] HELP! VBA to count number of coloured cells in a column and paste result in seperate tab
    By jordanleewillis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2016, 08:29 AM
  5. Count values in column but exclude filtered rows
    By elee532 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2013, 12:53 AM
  6. Replies: 4
    Last Post: 08-16-2012, 06:20 PM
  7. [SOLVED] Macro to filter active ws in workbook and paste filtered result in "result" worksheet
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2012, 12:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1