Results 1 to 4 of 4

Thread: [RESOLVED] Ecxcel vexing autofilter problem

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [RESOLVED] Ecxcel vexing autofilter problem

    I have this macro which is supposed to copy all yellow-colored cell to Range("A1") in the destination worksheet and I'm using autofilter since there are > 100,000 cells that MIGHT be yellow. I've verified that there are many cells colored yellow and many rows in column B and the colored cells do have the RGB(255, 255, 0) color, but nothing gets copied. WHY?
    Sub CopyColoredCellsUsingAutoFilter()
    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim rngSource As Range
    Dim rngFiltered As Range

    ' Set your source and destination worksheets
    Set ws = ActiveSheet
    Set wsDest = Sheet2

    ' Define the range where colored cells may be
    Set rngSource = ws.Range("B1:AW" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)

    ' Apply AutoFilter to the source range based on the yellow color
    rngSource.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

    ' Copy the visible (filtered) cells to the destination worksheet
    On Error Resume Next
    Set rngFiltered = rngSource.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not rngFiltered Is Nothing Then
    rngFiltered.Copy Destination:=DestWS.Range("A1")
    Else
    MsgBox "No colored cells found.", vbExclamation
    End If

    ' Turn off AutoFilter
    ws.AutoFilterMode = False

    End Sub

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,205

    Re: Ecxcel vexing autofilter problem

    The first thing to try is to disable the error handling you have in place and see what, if any, errors are being generated but suppressed. That may reveal the underlying issue.

  3. #3

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: [RESOLVED] Ecxcel vexing autofilter problem

    Note the "Filter on Color" in Excel is actually broken. It will not always show ALL of the cells with a certain color when you filter on a color. Sometimes, it will not even show the color you want to filter on. To work around this, I generally have a column that I can use to filter on something else (cell contents) that WILL always work.
    Last edited by jdc2000; Feb 28th, 2024 at 10:32 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width