• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

Naming a VBA selected range (1 Viewer)

sportsguy

Finance wiz, Access hack
Local time
Today, 13:08
Joined
Dec 28, 2004
Messages
346
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?

Code:
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
 

Micron

AWF VIP
Local time
Today, 13:08
Joined
Oct 20, 2018
Messages
3,379
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

Registered User.
Local time
Today, 18:08
Joined
Oct 1, 2009
Messages
2,341
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

Well-known member
Local time
Today, 10:08
Joined
Mar 14, 2017
Messages
1,460
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?

Code:
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.
 

Micron

AWF VIP
Local time
Today, 13:08
Joined
Oct 20, 2018
Messages
3,379
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

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 12:08
Joined
Feb 28, 2001
Messages
17,272
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

Well-known member
Local time
Today, 10:08
Joined
Mar 14, 2017
Messages
1,460
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:

Code:
?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. :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom