Named Range Operations

[abdul hafeel]

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

 

[pgc01]

Hi Abdul
Welcome to the board

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

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]

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 :

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]

Thanks Dominic & pgc. I need this to work out a multi coumn Vlookup function. I'll post the solution here for code pounding.

Cheers