Dynamic Named Range script is creating range based off the wrong column

[jmpatrick]

Good morning. I have been working with this piece of code to create a named range. I want to only scan Column L (12) for any text value and create the named range that it based only on values (ignoring formulas). The script below does create a named range with the correct name, but it seems to be determining the last row with a value on Column A instead of Column L which creates a named range with unwanted extra spaces.

Here's the code:

Sub SendFloorRequestRange()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Dim myWorksheet As Worksheet
 
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
 
    Dim myNamedRangeDynamic As Range
 
    Dim myRangeName As String
 
    Set myWorksheet = ThisWorkbook.Worksheets("SendFloorRequest")
 
    myFirstRow = 1
    myFirstColumn = 12
 
    myRangeName = "FloorPlanRequestRange"
 
    With myWorksheet.Cells
 
        myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        myLastColumn = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 
        Set myNamedRangeDynamic = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
 
End With
 
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

 

[johnnyL]

Try replacing:

myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

with:

myLastRow= myWorksheet.Range("L" & Rows.Count).End(xlUp).Row

 

[jmpatrick]

Try replacing:

myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

with:

myLastRow= myWorksheet.Range("L" & Rows.Count).End(xlUp).Row

That is keying off the formulas, not the text. How do I ignore the formulas using your code?

 

[johnnyL]

How about:

myLastRow= myWorksheet.[match(2,1/(l:l<>""))]

Those are 'L's after the 2,1/ part.

 

[jmpatrick]

How about:

myLastRow= myWorksheet.[match(2,1/(l:l<>""))]

Those are 'L's after the 2,1/ part.

Genius. Works very well. Thanks.

 

[johnnyL]

Glad to help.