Named Range Operations

abdul hafeel

New Member
Joined
Oct 14, 2006
Messages
9
Hi,

I need to find the 1st, last & Nth cell address of a Named Range. Also I want to know If I can resize a named range on the fly ie given certain criteria. Pls let me know
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Abdul
Welcome to the board

This is a way to get the 4 corners of a named range. Assuming its name is "MyRng":

Code:
Dim rTopLeft As Range, rTopRight As Range, rBottomLeft As Range, rBottomRight As Range

Set rTopLeft = Range("MyRng").Cells(1, 1)
Set rTopRight = Range("MyRng").Cells(1, Range("MyRng").Columns.Count)
Set rBottomLeft = Range("MyRng").Cells(Range("MyRng").Rows.Count, 1)
Set rBottomRight = Range("MyRng").Cells(Range("MyRng").Rows.Count, Range("MyRng").Columns.Count)

About the rest:

What do you mean by the Nth cell. The Nth cell on the first row? The Nth cell on the first column? The Nth cell counting by rows? The Nth cell counting by columns? Please specify.

Also please talk a bit more about the resizing. Giving examples helps understanding what you want.

Hope this helps
PGC
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good morning abdul hafeel

It's actually not that difficult to resize a named range. This code will reduce the number of rows in a range by one. Do you want to add this sort of code to four buttons to increase / decrease the rows / columns? You will need to tinker with the rows / columns to accomplish the other three directions :

Code:
Sub test()
With Range("MyNamedRange")
.Resize(.Rows.Count - 1, .Columns.Count).Name = "MyNamedRange"
End With
Application.GoTo Reference:="MyNamedRange"
End Sub

HTH

DOminicB
 

abdul hafeel

New Member
Joined
Oct 14, 2006
Messages
9
Thanks Dominic & pgc. I need this to work out a multi coumn Vlookup function. I'll post the solution here for code pounding.

Cheers
 

Forum statistics

Threads
1,141,718
Messages
5,708,072
Members
421,543
Latest member
SGM

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