need some help.... can't get this single line of code to work (that defines a named range...)

[kbishop94]

I'm having trouble writing this single line of code correctly so that it works and it doesnt give me an error: (errror = "Method of Range of Object_Worksheet failed")

(this does NOT work: )

Set TempRange = qq.Range("A1:" & "LastRow & LastColumn")

I have another similar line of code in a different module that does work. I am trying to modify that one to work for this module. The one that works only finds the first blank row in the range, and the new one (the one I am tyring to get to work) I need it to find both the first empty row AND the first empty column.

Here is the code that DOES work that I am currently trying to change up...

(this does work: )

Set TempRange = ztt.Range("A18:AW" & LastRow)

And here is the error that I keep getting: (along with showing some of the iterations of writing the line of code... none of which worked correctly.)


And in case it helps or provides any additional info for what I am trying to do, here is the entire code for this section:

'
' UPDATE THE NAMED RANGE ""Dynamic Tracker Range" WHEN A NEW ROW/COLUMN IS ADDED TO THE "TRAINING TRACKER" WORKSHEET:
    Dim qq As Worksheet
    Set qq = Worksheets("Training Tracker")
    Dim TempRange As Range
    Dim LastRow As Long
    'LastRow = qq.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
    LastRow = qq.UsedRange.Rows.Count
    Dim LastColumn As Long
    LastColumn = qq.Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
    Dim myRangeName As String
    ' *none of these iterations work:*
    'Set TempRange = qq.Range("A1:" & LastRow, LastColumn)
    'Set TempRange = qq.Range("A1:" & LastRow & LastColumn)
    'Set TempRange = qq.Range("A1:" & "LastRow:LastColumn")
    'Set TempRange = qq.Range("A1:" & "LastRow, LastColumn")
    Set TempRange = qq.Range("A1:" & "LastRow & LastColumn")
    
    myRangeName = "DynamicTrackerRange2"
'Creates the new named range with workbook scope. Defined name and cell range are as specified
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=TempRange
'

Thanks for any help that is offered!!

 

[Michael M]

Set TempRange = qq.Range(cells(1,1), cells(Lastrow, Lastcolumn))

 

[MARK858]

Try..

Set TempRange = qq.Range("A1", qq.Cells(LastRow, LastColumn))

@Michael M the code in post 2 will error if Worksheets("Training Tracker") isn't the ActiveSheet

 

[kbishop94]

Set TempRange = qq.Range(cells(1,1), cells(Lastrow, Lastcolumn))

Thank you Michael M

 

[MARK858]

Thank you Michael M

As I stated in post number 3, that code will error if Worksheets("Training Tracker") isn't the ActiveSheet when the code is being run

 

[kbishop94]

As I stated in post number 3, that code will error if Worksheets("Training Tracker") isn't the ActiveSheet when the code is being run

Thank you for pointing that out, MARK858.
At the beginning of this procedure in this module I do have the code specifically activating this worksheet: (I'm showing just the very first part of that section as its a pretty long part of the code... at the very end of this section is where the code that you and Michael M helped me with is positioned.)

'
'*************************************************************************************************************************************
'****************************************                                                     ****************************************
'****************************************             TRAINING TRACKER SECTION                ****************************************
'****************************************                                                     ****************************************
'*************************************************************************************************************************************
'
'****************
ActiveWorkbook.Worksheets("Training Tracker").Activate
'
bRow = Sheets("Training Tracker").UsedRange.Rows.Count
'
With tt
' This selects the 2nd to the last 2 columns over from the end

 

[MARK858]

If you are relying on Activating the sheet then all you need is

Set TempRange = Range(Cells(1,1), Cells(Lastrow, Lastcolumn))

and there is no point setting and using the variable qq (not that I am recommending doing this)

 

[kbishop94]

If you are relying on Activating the sheet then all you need is

Set TempRange = Range(Cells(1,1), Cells(Lastrow, Lastcolumn))

and there is no point setting and using the variable qq (not that I am recommending doing this)

Got it. Thank you again.

Set TempRange = ActiveWorkbook.Worksheets("Training Tracker").Range(Cells(1, 1), Cells(LastRow, LastColumn))

 

[MARK858]

Got it. Thank you again.

Not sure that you have judging by the last line of the post I'll try and explain just so you do when using this syntax in future.

The code you have has 3 parts

ActiveWorkbook.Worksheets("Training Tracker").Range(

Which tells Excel you are going to create a range

and 2 separate ranges

Cells(1, 1)

and

Cells(LastRow, LastColumn)

Now a range can only be on a single worksheet and so if you are referring to ranges that are not on the activesheet you need to state the appropriate worksheet for each individual range.

so what is the code saying to excel?

We are going to pretend that you have another sheet called "NotMe" and assign it to the variable xx, we are going to keep Worksheets("Training Tracker") as variable qq.

so you have the code below at the moment

Set qq = Worksheets("Training Tracker")
Set xx = Worksheets("NotMe")
qq.Range(Cells(1, 1), Cells(LastRow, LastColumn))

Now pretend the ActiveSheet is currently sheet xx rather than sheet qq.

Because Excel isn't being told what sheet either of the 2 ranges are on it assumes they both should be on the Activesheet, which in this scenario is sheet xx so you get

qq.Range(xx.Cells(1, 1), xx.Cells(LastRow, LastColumn)

As I stated earlier you can't have a range that refers to multiple sheets and so the above will error in this scenario

to get it to work you would need to do one of the following

qq.Range(qq.Cells(1, 1), qq.Cells(LastRow, LastColumn)

Range(qq.Cells(1, 1), qq.Cells(LastRow, LastColumn)

or

qq.Range("A1", qq.Cells(LastRow, LastColumn)

The last one works because "A1" is a string and not a range and so Excel treats that part exactly the same as it would with a normal fixed range and links it to the qq in qq.Range.

All of the above makes no difference with your code today as you are activating Worksheets("Training Tracker") first, but it will make a difference in future code if you are not activating the sheet the range applies to before you reach the code line

I hope that clears it up a bit (even with my poor description skills)