VBA to Use Highlighted Data as A Defined Name

[jhabey01]

Hi,
I have the following code which just highlights all the data in a worksheet. I want to take that data that is highlighted and use it to define a name such as CurrentMTH, I have a few things none of which are working. Any thoughts?

Sub DynRange()
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("CURRENTMONTH")
Set StartCell = Range("a1")
'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
End Sub

 

[Andrzejek]

Could you be more specific in what you mean by: "take that data that is highlighted and use it to define a name"?

Let's say your data consists of 10 columns and 15 rows (150 cells), all of it is highlighted (selected). What do you want to happen?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jhabey01]

Sure,
I want use the data selected and create a defined name called CurrentMTH. The defined named will be that data that was selected all 150 cells will now be a definednamed called CurrentMth

 

[combo]

Workbook level name:

[pre]Selection.Parent.Parent.Names.Add Name:="CurrentMTH", RefersTo:=Selection[/pre]

Or without selecting:

[pre]With sht
.Parent.Names.Add Name:="CurrentMTH", RefersTo:=.Range(StartCell, .Cells(LastRow, LastColumn))
End With[/pre]

combo

 

[Andrzejek]

What combo is referring to (I guess....) is - when you know how to do it 'by-hand' and you want to know how to do it 'in code', the best way is to turn on the Macro recorder, do the step(s), turn Macro recorder off and look at the code (Alt-F11)

Some time you need to modify the code a little (usually you can shorten the code).

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

jhabey01,

So there's an answer and maybe there's a solution. Hence a question.

Is this a TABLE, headings and all, and if so, do you want a defined name to reference the DATA in the table or do you want a named range to reference the entire TABLE?

Either way, consider using the Structured Table feature, introduced in Version 2007: Insert > Tables > Table. The entire table is named Table1 by default assuming this is the first Structured Table in this workbook. The default name can be changed. The DATA in the table is defined as Table1[#Data].

As rows are added or deleted, the references automatically change to encompass the entire table. Formulas automatically propagate to new rows. And there are many other neat features to be discovered.

I highly recommend exploring, becoming familiar and using Structured Tables and references.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Thanks I will take a look.
John