Naming a VBA selected range

[sportsguy]

so far, All examples have failed, and yet, I don't exactly know why. I have extracted data from a SQL Server onto a tab named DATA, the row count will be different each time, but the column count is fixed. I highlight the data and am trying to assign the highlighted range to the name PIVOTDATA for a pivotable range to dynamically adjust to the data range.

What am I missing?

Sub RenameRange()
On Error GoTo Err_RenameRange

Dim LastRow As Long
Dim LastColumn As Long
Dim NamedRangeDynamic As Range


ActiveWorkbook.Names("PIVOTDATA").Delete
Worksheets("ACT").Activate
Set NamedRangeDynamic = ActiveSheet.Range("A1", ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ThisWorkbook.Names.Add Name:="PIVOTDATA", RefersTo:=NamedRangeDynamic

Set NamedRangeDynamic = Nothing

Exit_RenameRange:
    Exit Sub
   
Err_RenameRange:
    If Err.Number = 1004 Then Resume Next
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_RenameRange

End Sub

 

[Deleted member 147267]

Didn't look too much at your code because I had a different approach that worked every time. I used a named dynamic range in Excel. The range size changes as rows are added/deleted, so from Access all I had to do was refer to the range name. There was one caveat that I had to figure out; named ranges can be at the workbook and sheet level. IIRC, my named range had to be at the sheet level.

If that doesn't interest you, then someone more familiar with Excel vba might chime it - but it might help to state what result you're getting: error messages or whatever. Perhaps it is as simple as not saving the workbook before you set the range to Nothing.

 

[Trevor G]

Have you considered two different methods:

One convert the data into a Table which then deals with additional rows and columns
Two use CurrentRegion which selects all the cells and you should then add the named range

Range("A1").CurrrentRegion.Select

 

[Isaac]

so far, All examples have failed, and yet, I don't exactly know why. I have extracted data from a SQL Server onto a tab named DATA, the row count will be different each time, but the column count is fixed. I highlight the data and am trying to assign the highlighted range to the name PIVOTDATA for a pivotable range to dynamically adjust to the data range.

What am I missing?

Sub RenameRange()
On Error GoTo Err_RenameRange

Dim LastRow As Long
Dim LastColumn As Long
Dim NamedRangeDynamic As Range


ActiveWorkbook.Names("PIVOTDATA").Delete
Worksheets("ACT").Activate
Set NamedRangeDynamic = ActiveSheet.Range("A1", ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ThisWorkbook.Names.Add Name:="PIVOTDATA", RefersTo:=NamedRangeDynamic

Set NamedRangeDynamic = Nothing

Exit_RenameRange:
    Exit Sub
  
Err_RenameRange:
    If Err.Number = 1004 Then Resume Next
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_RenameRange

End Sub

1. Can you be more specific about what happens when you run your code? i.e., Run time error, Executes but results are different than you expect, or Something else. ??
2. Your error handler. If err.number=1004, then essentially the error is being totally ignored, so you wouldn't even know about it. Is this what you intend? Why?. If err.number<>1004, then all that is happening is a debug.print and essentially exiting the sub. Is this occurring?

I would recommend commenting out On Error GoTo Err_RenameRange entirely in order to allow for a runtime error to properly be presented, then click Debug, and let us know what the error is, and which line of code is highlighted.

 

[Deleted member 147267]

I can't see why you're trying to work with a dynamic range through vba when you can let Excel handle it internally. Maybe it's just a challenge you can't pass up?

 

[The_Doc_Man]

While there are literally MYRIADS of reasons why things go wrong, error 1004 is "special." You see it for application objects when you have somehow disqualified something that in its original context was correctly qualified. I.e. If you have a qualified reference similar to app.file.element.cell then it is POSSIBLE that one of those qualifiers isn't valid any more. As a result, the qualified reference has become dereferenced. Unfortunately, it is very hard to track these down.

You might need to do some single-step debugging in the vicinity of the offending code to see if/when something becomes Nothing. If you can find when that occurs, you will be ahead of the game in debugging.

 

[Isaac]

While there are literally MYRIADS of reasons why things go wrong, error 1004 is "special." You see it for application objects when you have somehow disqualified something that in its original context was correctly qualified. I.e. If you have a qualified reference similar to app.file.element.cell then it is POSSIBLE that one of those qualifiers isn't valid any more. As a result, the qualified reference has become dereferenced. Unfortunately, it is very hard to track these down.

You might need to do some single-step debugging in the vicinity of the offending code to see if/when something becomes Nothing. If you can find when that occurs, you will be ahead of the game in debugging.

Where it has sometimes gotten me is when I have asked Excel to do something nonsensical like an offset that mentions the row above row 1, like this Immediate Window sample:

?thisworkbook.Worksheets("Sheet1").range("A1").offset(-1,0).value

Either way - definitely NOT something that I think one would ever want to have an error handler that said, If that error occurs, just Resume Next.