Define Names in Workbook through Code
 

Hi Gurus,

I'm trying to find a way for Excel to lump various columns into
different named ranges based on the names or headings of the columns.
There will be five different types of columns that will repeat
horizontally, eventually extending to about column 200, and each column
with the same heading should be included in its corresponding named
range.

Instead of manually having to do something like...
Range("U:U,Y:Y").Select
Range("U:U,Y:Y,AC:AC").Select
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO").Select
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW").Sel ect
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW,BA:BA ").Select

Can Excel do it automatically?

Thanks,
Arnold

Define Names in Workbook through Code
 

Since the data is laid out nicely, I'd just count starting at the first column
and ending at the last.

I used column U as the first and BA as the last.

Option Explicit
Option Base 0
Sub testme()

Dim myRngs As Variant
Dim iCtr As Long
Dim rCtr As Long
Dim myNames As Variant
Dim myStep As Long

myNames = Array("Name01", "Name02", "Name03", "Name04", "Name05")

ReDim myRngs(LBound(myNames) To UBound(myNames))

For rCtr = LBound(myRngs) To UBound(myRngs)
Set myRngs(rCtr) = Nothing
Next rCtr

myStep = UBound(myNames) - LBound(myNames) + 1
With ActiveSheet
For iCtr = .Range("u1").Column To .Range("BA1").Column Step myStep
For rCtr = LBound(myRngs) To UBound(myRngs)
If myRngs(rCtr) Is Nothing Then
Set myRngs(rCtr) = .Cells(1, iCtr + rCtr)
Else
Set myRngs(rCtr) _
= Union(myRngs(rCtr), .Cells(1, iCtr + rCtr))
End If
Next rCtr
Next iCtr
End With

For rCtr = LBound(myRngs) To UBound(myRngs)
myRngs(rCtr).EntireColumn.Name = myNames(rCtr)
Next rCtr

End Sub


Arnold wrote:

Hi Gurus,

I'm trying to find a way for Excel to lump various columns into
different named ranges based on the names or headings of the columns.
There will be five different types of columns that will repeat
horizontally, eventually extending to about column 200, and each column
with the same heading should be included in its corresponding named
range.

Instead of manually having to do something like...
Range("U:U,Y:Y").Select
Range("U:U,Y:Y,AC:AC").Select
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO").Select
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW").Sel ect
Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW,BA:BA ").Select

Can Excel do it automatically?

Thanks,
Arnold

--

Dave Peterson

Define Names in Workbook through Code
 

Hi Dave,

Thanks for the reply and code. The columns will, however, not be laid
out so nicely--that is, they will not go in any particular order
horizontally across the sheet. Also, there will be more of some
columns than others. Will the code account for this? I'll try it as
soon as I can.

Thanks.

Define Names in Workbook through Code
 

Option Explicit
Sub testme()

Dim myRng As Range
Dim myTitles As Variant
Dim myNames As Variant
Dim iCtr As Long
Dim FoundCell As Range
Dim FirstAddress As String

myNames = Array("Name01", "Name02", "Name03", "Name04", "Name05")
myTitles = Array("a", "what you want1", "another one", _
"next one", "last one here")

If UBound(myNames) < UBound(myTitles) Then
MsgBox "design error!"
Exit Sub
End If

With ActiveSheet
For iCtr = LBound(myTitles) To UBound(myTitles)
FirstAddress = ""
Set myRng = Nothing
With .Rows(1) 'row with header
Set FoundCell = .Cells.Find(what:=myTitles(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox myTitles(iCtr) & " wasn't found!"
Else
FirstAddress = FoundCell.Address
Set myRng = FoundCell
Do
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Set myRng = Union(myRng, FoundCell)
Loop
myRng.EntireColumn.Name = myNames(iCtr)
End If
End With
Next iCtr
End With
End Sub


Arnold wrote:

Hi Dave,

Thanks for the reply and code. The columns will, however, not be laid
out so nicely--that is, they will not go in any particular order
horizontally across the sheet. Also, there will be more of some
columns than others. Will the code account for this? I'll try it as
soon as I can.

Thanks.

--

Dave Peterson

Define Names in Workbook through Code
 

Thanks Dave--it worked great.

Define Names in Workbook through Code
 

One more question,

I have a question about the use of these ranges....I was wanting to do
the above so that I could automatically get the addresses of all the
cells in all the columns for a certain thing, like "Days". However, I
need to be able to do tallies and other simple formulas across the
sheet for individual rows (1 person per row).

For instance, one of the ranges of columns was named "Day", and for
every Day column that was included in the range, add the values of the
cells (0, 1, 2, 3, 4, or 5), skipping any cells that are all text (ie.
x or X). Then I could copy this formula down for each person. This
would give each person's total score for all the days they were present
(ommitting the "x" days).

Is there a way to get the range from the Define Name into a formula,
but replacing entire $columns with just the cells in the columns across
one row. Does this make sense?

Define Names in Workbook through Code
 

You could use range names (but that sounds pretty awkward (to me, anyway)).

Maybe better would be to use =sumif()

=sumif($a$1:$AB$1,"Some title",$a2:$ab2)



Arnold wrote:

One more question,

I have a question about the use of these ranges....I was wanting to do
the above so that I could automatically get the addresses of all the
cells in all the columns for a certain thing, like "Days". However, I
need to be able to do tallies and other simple formulas across the
sheet for individual rows (1 person per row).

For instance, one of the ranges of columns was named "Day", and for
every Day column that was included in the range, add the values of the
cells (0, 1, 2, 3, 4, or 5), skipping any cells that are all text (ie.
x or X). Then I could copy this formula down for each person. This
would give each person's total score for all the days they were present
(ommitting the "x" days).

Is there a way to get the range from the Define Name into a formula,
but replacing entire $columns with just the cells in the columns across
one row. Does this make sense?

--

Dave Peterson