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

 

[Darren Smith]

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 ?

The address is on GantryID worksheet A1 to C5

 

[NoSparks]

Is that the answer to the first or second question?

 

[Darren Smith]

Second I think

 

[mikerickson]

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

As a preliminary, note the distinction between a Range object and the .Range property of an object.

Many type of objects have a .Range property. This observation is about the differences between the .Range property of an Application object and of a Worksheet object (and to a lesser degree, as a property of a Range argument)

There are two forms of the .Range property. One form takes a single string argument, eg. Range("A1")
The other form takes two arguments, typically two Range objects, e.g. Range(aCell, bCell)
Both forms return an Range object.

The question arises, "on what sheet is the resulting range object?"

In the one argument form, the parent worksheet is determined by which object the .Range property is a member of.

A typical line of code is often unqualified (i.e. the default Application qualification)

Set myCell = Range("A1")
' rather than then more fully (no defaults)
Set myCell = Application.Range("A1")

which sets myCell to be on the worksheet that is the ActiveSheet.
For many reasons, it is recommended that the coder qualify that line whenever possible.

Set myCell = Sheets("Sheet1").Range("A1")

The unqualified statement returns the .Range property of the Application object. The Qualified statement returns the .Range property of a worksheet object.

That's the one argument form of the .Range property.

The two argument .Range property returns the Range object bound by the two arguments, both of them Range objects.
A Range object must have all of its cells on the same worksheet.
This is the cause of many errors (Lots of threads about this problem)
We see many threads where the coder will use code like this to return Sheet1!$A$1:$B$10 and wonders why it sometimes errors.

Set myCells = Sheets("Sheet1").Range(Range("A1"), Range("B10"))

The reason why that errors is that the Range("A1") and Range("B10") are unqualified (i.e. properties of the Application object) and are, therefore, on the ActiveSheet. If Sheet1 is not the active sheet, the range bound by those two cells is not on Sheet1. Thus the error when one tries to make that not-on-Sheet1 range a property of the Sheet1 object.

If the coder reverses things and uses the (unstated) .Range property of the Application object for the outer .Range, and fully qualified inner arguments that come from properties of a worksheet object, the result will defiantly be on Sheet1 and the code will never error.

Set myCells = Range(Sheets("Sheet1").Range("A1"), Sheets("Sheet1").Range("B10"))

The upshot of all of this is that I treat the two argument form of a .Range property almost as a function rather than as a property. Qualifying (setting "which sheet") of the two argument form of Range is done by the arguments, not by the parent object.

 

[RoryA]

the result will defiantly be on Sheet1 and the code will never error

Unless that code is in the code module of a different sheet. Personally I recommend always qualifying all range/cells properties, unless they are in a worksheet and you intend to refer to that sheet.

 

[mikerickson]

Good catch.

 

[Darren Smith]

Sorry, but what you said above is for a static range is that correct?
I need a dynamic range...

 

[RoryA]

Your range is effectively static because you are calculating it in code, not using formulas.

 

[Darren Smith]

I would like to add access database detail straight into the Combobox rather than pasting it to a worksheet then fill it into Combobox?
I have raised a question for this on this forum.
Sorry I appreciate everyone's help though