named range and lastrow

[pwwato]

Hi

Thanks for taking the time to look at this and any help you give.

I am trying to take a name range TRange = ("A2:D") and resize it to include the last row of the sheet, I have the code for last row which passes to a variant called lastrow for the sheet I am doing this on ,the last row would be 92 but this is a function I want to run on various sheets with different size last rows so need to change the range depending on the sheet A2:D will always be the starting range to be passed to this function.

I have tried a few ways of doing this but unable to get it right this is the way im trying it at present but I don't seem to be able to get it quite right.
application defined or object defined error

Range(TRange.Name).Resize(lastrow, Range(TRange.Name).Columns.Count).Resize.Name = TRange.Name[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

​

 

[Fluff]

Try

Range("TRange").Resize(Lastrow, Range("TRange").Columns.Count).Name = "TRange"

 

[pwwato]

Thanks
Still not working tho i get a method range of object global failed, i know the TRange that is passed from the sub to the function is right because if i put TRange.select it selects A2:D on the worksheet but if i put your code or mine after this it fails?

 

[Fluff]

What is the scope of the named range? workbook, or sheet

 

[pwwato]

The range is passed from a sub in vba along with the workbook and sheet values

Dim TBook As String

Dim TSheet As String

Dim TRange As Range

Dim TArray() As Variant

TBook = ("yesdatav2.xlsm")
TSheet = ("sites")
Set TRange = Range("A2:N2")
DO2T = 2
 

Call create_array(TBook, TSheet, TRange, DO2T, TArray)

Function

Public Function create_array(TBook As String, TSheet As String, TRange As Range, DO2T As Integer, ByRef TArray As Variant) As Variant


Dim lastrow As String

Application.Workbooks(TBook).Sheets(TSheet).Activate
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

The intension for this is to create dynamic arrays that i can pass back to the sub for further manipulation searching/sorting ect

Thanks

 

[Fluff]

TRange is not a named range, but a range variable.
Without being able to see your entire code, or knowing what you are trying to achieve, it's difficult to help.
but does this get you any closer

TRange.Resize(lastrow, TRange.Columns.Count).select

 

[pwwato]

Thanks for that fluff, that worked sorry for the named range issue i sort of got sidetracked looking for answers..

I tried this after selecting the range about 3 hrs ago but it didnt work.

TRange.cells(lastrow, TRange.Columns.Count)

Close but no cigar!!

Thanks again.

 

[Fluff]

Glad to help & thanks for the feedback