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:
Here's the code:
VBA 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
