• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to name a range based on the data selected.

ThrottleWorks

ThrottleWorks

Excel Ninja
Sir, I want to name a range, this range will be used in further processing.

The range is flexible, it will change with every run.


I am able to select the range by using following code.


Cells(2, i).Offset(0, 0).Select

ActiveCell.Resize(b + 1, d).Select


Can anyone please tell me how do I name this range using VBA.
 
Good day sachinbizboy


Why not select the range click in name the range box, name it click enter and be done when you select the named range name it will take you to the data no matter where it is in your sheet
 
Hi bobhc, thanks for the reply.


Have a nice day to you too.


I cannot use the manual option to name the range.

I need this range to use in a formula.

When I am using this formula, I want the range name.


I want to do it by vba because the end used is going paste data in one sheet & submit.

Once the data is pasted, data selecting processing has to be done by VBA only.
 
Hi bobhc, I am trying


Rng = Cells(2, i).Offset(0, 0).Resize(b + 1, d).Select


I am trying if this is working, will definately share the results.


Thanks a lot for the help.
 
Hi Sachin ,


This article has a section on using VBA to create named ranges :


http://www.cpearson.com/excel/DefinedNames.aspx


Narayan
 
Hi bobhc & Narayan Sir,


I was able to it in the following way.


Sub CreateName()


a = Worksheets("view").Range("g6").Value


b = Worksheets("view").Range("g7").Value


c = b + 5


Cells(2, c).Select


ActiveCell.Resize(a + 1, b).Name = "dataTable"


Once again, thanks a lot for your support & have a nice day.
 
Hi Sachin ,


You can make it short , by not selecting any cell ; of course , you need to select the Worksheet labelled "view" beforehand , either manually , or within your VBA procedure :

[pre]
Code: 
With ActiveSheet
ThisWorkbook.Names.Add Name:="dataTable", _
RefersTo:=Cells(2, .Range("g7").Value + 5).Resize(.Range("g6").Value + 1, .Range("g7").Value)
End With
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top