This code is to alter the named Range size

[Darren Smith]

It says the Method Range of object worksheet failed.
What do I have to alter?
The "GantrySizes" is the named range

Private Sub Gantry_Height_Width_Change()

Dim ws As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set ws = ThisWorkbook.Worksheets("GantryID")
Set StartCell = ws.Range("A1")

  LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column
 
ws.Range("GantrySizes").Value = ws.Range(StartCell, ws.Cells(LastRow, LastColumn))

End Sub

 

[RoryA]

It suggests you don't have a range called GantrySizes on the GantryID sheet.

 

[Darren Smith]

It is there but every time I try to run the code it deletes the table I`ve just realized

 

[mikerickson]

The code that you have there is trying to put the values from ws.Range(StartCell, ws.Cells(LastRow, LastColumn)) into the Named Range GantrySizes. If those two ranges aren't the same size, bad things happen.

The title of the thread mentions altering the size of a named range. If you are trying to change the cells that are in the named range, you might use this syntax

ws.Range(StartCell, ws.Cells(LastRow, LastColumn)).Name = "GantrySizes"

 

[Darren Smith]

I tried what you advised but it now says the "Wrong number of arguments or invalid property assignment"

Private Sub Gantry_Height_Width_Change()

Dim ws As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set ws = ThisWorkbook.Worksheets("GantryID")
Set StartCell = ws.Range("A1")

  LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column
  
ws.Range(StartCell, ws.Cells(LastRow, LastColumn)).Name = "GantrySizes"

End Sub

 

[NoSparks]

Are you sure you are taking the right route to achieve what you are wanting to do?
Without code, or referring to code that doesn't work, can you tell us in words what you are trying to accomplish?

 

[Darren Smith]

There are details from an access spreadsheet that fills into the excel spreadsheet. This part works fine
I want the named range to change size according to the amount of data filled into the excel spreadsheet

 

[NoSparks]

What is the address of the existing named range GantrySizes ?
Where (what address range) are the details from the access spreadsheet table being written to in the spreadsheet ?

 

[mikerickson]

Try

Range(StartCell, ws.Cells(LastRow, LastColumn)).Name = "GantrySizes"

The two argument form of Range really shouldn't be qualified to a sheet. Its unnecessary. (omit long rant masquerading as an explanation.)

 

[Darren Smith]

(omit long rant masquerading as an explanation.)
Please explain