VBA Create Range Not Working - What Am I doing Wrong?

[Shiseiji]

As always, TIA to those who give of their time and expertise. I can't figure out why the range isn't being created. I expect it will be a flat forehead moment when someone else looks at it.

Ron

The KISS record a macro code is:

Range("J2:P2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="tbl_ws_Report", RefersToR1C1:= _
        "='Accounts Report'!R2C10:R15751C16"

What I typically use is a little simpler with all rows and all columns. This is erroring on the range statement.

Sub m_ws_Report_Ranges()
'20211006
    '
  With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "Making Report Ranges"
    .Volatile
    .DisplayAlerts = False
  End With 'Application
    '
    Dim ws_ReportLastRow          As Long
    Dim tbl_ws_Report             As Range
    Dim ThisWb                    As Workbook
    Dim ThisWs                    As Worksheet
    '
  Set ThisWb = ActiveWorkbook
  With ThisWb
  ws_summary.Activate
    Set ThisWs = ActiveSheet
'--Start delete old range names
    ThisWb.Names("tbl_ws_report").Delete
'--End delete old range names
'-- Start Name Report ranges --
  ws_report.Activate
  Set ThisWs = ActiveSheet
  With ThisWs
'--Note: column J is email records, no null cells, no formulas
  ws_ReportLastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
  Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"
'-- End Name Report ranges --
  End With 'ThisWs
  ws_summary.Activate
  With ThisWs
  Application.ScreenUpdating = True
  Range("A1").Activate
  ActiveWindow.SmallScroll down:=3
  ActiveWindow.SmallScroll down:=-3
  Range("A1").Activate
  End With 'ThisWS
  With Application
    .ScreenUpdating = True 'False
    .EnableEvents = True 'False
    .DisplayAlerts = True ' False
    .Calculation = xlCalculationAutomatic 'xlManual
    .StatusBar = " "
    .StatusBar = "Completed creating Report Ranges."
  End With 'Application
  End With 'ThisWb
End Sub

 

[Rick Rothstein]

Maybe this does what you are looking for...

Range("J2:P" & Columns("J:P").Find("*", , xlValues, , xlRows, xlPrevious).Row).Name = "tbl_ws_Report"

 

[Shiseiji]

Try this instead

  ThisWb.Names.Add Name:="tbl_ws_Report", RefersTo:=Range("J2:P", ws_ReportLastRow)

Afraid still no joy. At least I'm not crazy. First time I've tried to create a range not starting on column "A" Yet the VBA really isn't any different that then macro record.

Looking at it again, your original line

Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"

may have a typo. Try this instead

Range("J2:P" & ws_ReportLastRow).Name = "tbl_ws_Report"

Argggggg "&" Oh my. Look at things a gazillion times . . . . ='Accounts Report'!$J$2:$P$15751

Thank you for sticking with this!

Ron

 

[Shiseiji]

Maybe this does what you are looking for...

Range("J2:P" & Columns("J:P").Find("*", , xlValues, , xlRows, xlPrevious).Row).Name = "tbl_ws_Report"

Yes! And thanks to you too sir! "&" You have personally helped me so much over the years, I don't think I can ever pay it all forward.

Thank you!

Ron

 

[rlv01]

Glad we could finally stumble across the solution