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
Bookmarks