This code is to alter the named Range size

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
It says the Method Range of object worksheet failed.
What do I have to alter?
The "GantrySizes" is the named range


VBA Code:
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
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,829
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It suggests you don't have a range called GantrySizes on the GantryID sheet.
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
It is there but every time I try to run the code it deletes the table I`ve just realized
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,083
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

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

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Mar 15, 2013
Messages
1,279
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 15, 2013
Messages
1,279
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,083
Try
VBA Code:
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

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
(omit long rant masquerading as an explanation.)
Please explain
 

Forum statistics

Threads
1,148,346
Messages
5,746,198
Members
423,998
Latest member
Nancy2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top