Select a range within a column

Hello jomili,

Maybe this code is what you need...

  Dim Rng As Range
  Dim Wks As Worksheet
    
    Set Wks = Worksheets("Lookups")
    Set Rng = Wks.Range(Wks.Cells(2, "F"), Wks.Cells(Rows.Count, "F").End(xlUp))
    
    ThisWorkbook.Names.Add "PertPacs1", Rng
    Addx = Range("PertPacs1").Address

I've got the PertPacs1 working for View 2, but am now having trouble with View3, as the code I'm using doesn't appear consistent in this view. Here are the pertinent macros:

These first two are the good, working macros for View2:

This one Filters the first column, creates named range for second - works well

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim ws As Worksheet:    Set ws = Sheets("View2Pivot")
Application.ScreenUpdating = False

    For Each Cell In Target
        If Not Intersect(Cell, Range("E1:E2")) Is Nothing Then
            ws.AutoFilterMode = False
            If [E1] <> "" Then ws.Range("L:M").AutoFilter field:=1, Criteria1:=[E1].Value
            DoEvents
            Run "PertPacsOne"
            
            If [E2] <> "" Then ws.Range("L:M").AutoFilter field:=2, Criteria1:=[E2].Value
        End If
    Next Cell

Application.ScreenUpdating = True
End Sub

This one adds the range for my second column to my "temp" worksheet, deletes the duplicates, defines the name range - works well

Sub PertPacsOne()
Dim Rng As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Temp")
Set Rng = Wks.Range(Wks.Cells(2, "A:A"), Wks.Cells(Rows.Count, "A").End(xlUp))

Wks.Range("A:A").ClearContents
Worksheets("View2Pivot").AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Copy Destination:=Wks.Range("A:A")
Wks.Range("A1").Delete Shift:=xlUp

Run "Useit"

ThisWorkbook.Names.Add "PertPacs1", Rng
    Addx = Range("PertPacs1").Address
    

End Sub

The next two are my attempts for View3, where we have 3 dropdown fields, so need 2 Named Ranges to populate the 2nd and 3rd dropdowns. They're basically the same as the code above, but they don't work and I can't figure out why.

Filter the first column, create named range for second - works intermittently; haven't even tried 3rd column yet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim ws As Worksheet:    Set ws = Sheets("View3Pivot")
Application.ScreenUpdating = False

    For Each Cell In Target
        If Not Intersect(Cell, Range("E1:E3")) Is Nothing Then
            ws.AutoFilterMode = False
            If [E1] <> "" Then ws.Range("N:P").AutoFilter field:=1, Criteria1:=[E1].Value
            DoEvents
            Run "PertPacsTwo"
            
            If [E2] <> "" Then ws.Range("N:P").AutoFilter field:=2, Criteria1:=[E2].Value
            'If [E3] <> "" Then ws.Range("N:P").AutoFilter field:=3, Criteria1:=[E3].Value
            
        End If
    Next Cell

Application.ScreenUpdating = True
End Sub

This one adds the range for my second column to my "temp" worksheet (it's quite alright that it overwrites what was previously there), deletes the duplicates, defines the name range - sometimes works, but usually defines the range as only containing A1, even when there are 3-4 cells in the column with discreet values. I haven't even attempted the third dropdown choice yet.

Sub PertPacsTwo()
Dim Rng As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Temp")
Set Rng = Wks.Range(Wks.Cells(2, "A:A"), Wks.Cells(Rows.Count, "A").End(xlUp))

Wks.Range("A:A").ClearContents
Worksheets("View3Pivot").AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Copy Destination:=Wks.Range("A:A")
Wks.Range("A1").Delete Shift:=xlUp

Run "Useit"

ThisWorkbook.Names.Add "PertPacs2", Rng
    Addx = Range("PertPacs2").Address
    

End Sub

I've attached my workbook, and please let me know of any other explanation I can provide.

Thanks,
John