Results 1 to 9 of 9

Advanced filter with blank cells / Dynamic named range with blank cells

Threaded View

  1. #1
    Registered User
    Join Date
    03-01-2007
    Posts
    47

    Advanced filter with blank cells / Dynamic named range with blank cells

    The following code filters unique values to a different sheet and fills columns of dynamic named ranges to populate some combo boxes in a user form.

    Sub CreateDynamicRange()
        Dim LR As Long
    
        LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
        Sheets("Data").Range("B6:B" & LR).AdvancedFilter Action:=xlFilterCopy, _
                                                            CopyToRange:=Sheets("CBList").Range("A1"), Unique:=True
        Sheets("Data").Range("C6:C" & LR).AdvancedFilter Action:=xlFilterCopy, _
                                                            CopyToRange:=Sheets("CBList").Range("C1"), Unique:=True
        Sheets("Data").Range("F6:F" & LR).AdvancedFilter Action:=xlFilterCopy, _
                                                            CopyToRange:=Sheets("CBList").Range("E1"), Unique:=True
                                                            
    End Sub
    The last column to be filtered sometimes has blank cells, and it seems the filter is counting that as a unique value. The problem is that the blank cell is at the top, and the dynamic named range does not skip over this blank cell to extend the named range to the valuable data in the list. Is there a way to change the advanced filter to eliminate blank cells or a way to force the dynamic named range to include at least two rows of data (forcing it to skip the blank cell)?

    The formula for the named range is:
    =OFFSET(CBList!$E$2,0,0,(COUNTA(CBList!$E:$E)-1),1)
    Thanks for the help,
    Jason
    Last edited by Jason_2112; 11-15-2010 at 12:06 PM.

Thread Information

Users Browsing this Thread

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

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