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

Thank you for pointing me in the right direction. I recorded a macro to determine the language I thought I should use to sort the columns of filtered data, but the code which I modified some, doesn't seem to be working. Here is the code for the entire macro:

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
    Sheets("CBList").Range("A1:A35").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
                    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
    Sheets("CBList").Range("C1:C35").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
                    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
    Sheets("CBList").Range("E1:E35").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
                    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
                    
                                                        
End Sub

The ranges are dynamic named ranges, but I don't think I can reference that in this code seeing as the blank cells in the list are causing the named ranges to not expand to include all of the necessary data. I included 35 rows in each, as I don't anticipate having more than that much data in each column.

Thank you for any help.

Jason

I am getting the following error message when I run the sort via VBA.

"The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank."

The data to be sorted is on the sheet "CBList" and the header for each column is in A1, C1, and E1.

What do I need to do to reference the appropriate range?

- Jason

Ok, I've figured out that the code will run by itself if the worksheet "CBList" is the active sheet, but if another sheet is active, and the macro is run from that sheet, it will not work.

Here is the code as I have it now.

Option Explicit
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
        
    Sheets("CBList").Range("A:A").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets("CBList").Range("C:C").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets("CBList").Range("E:E").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
                                  
End Sub

I ran the code for sorting as a separate macro, and it worked fine as long as the CBList sheet was active. Not sure what to do now.

- Jason

Thank you for all the help. That did the trick.

- Jason