Solved: Naming rows according to data in column A

[Sir Babydum GBE]

Hello my friends. Long time no see (my fault, sorry)

I have a very long list of names in column A. In column B onwards, I have data that relates to the names in column A. What I want to do is get a macro to look at the text in column A (e.g., A1 = "Babydum") then apply the name to all the cells in the same row (except column A) that have data in them (e.g. the selection B1 to G1 would be given the name "Babydum" so that I can find it in the names box.

Any ideas?

Cheers

[Killian]

Here's a way of doing thatSub AddRangeNames()

Dim c As Range
Dim nCols As Long

'set final column number for range
nCols = 2

'for all the rows A1 down (continuous data)
For Each c In Range("A1", Range("A1").End(xlDown))
'new range is from second col to end col
Range(c.Offset(0, 1), c.Offset(0, nCols)).Name = c.Value
Next

End Sub

[Sir Babydum GBE]

Killian,

Thanks for this. I tried it out at home. I had to remove all illegal characters from my lists so that they could be used as names. Then it worked great. It's a shame that I can't apply the name to a flexible number of columns - because some names have only one cell to the right, whereas others have seventy! I use the INDIRECT function on another sheet to give me access-style validation lists - which means that some of those lists appear blank until I scroll up. But that's a small price to pay given the amount of time your code saved.

Cheers

[Killian]

It's a shame that I can't apply the name to a flexible number of columns - because some names have only one cell to the right, whereas others have seventy!Well with a small change to the code, you can...Sub AddRangeNames()

Dim c As Range

'for all the rows A1 down (continuous data)
For Each c In Range("A1", Range("A1").End(xlDown))
'new range is from second col to last used
Range(c.Offset(0, 1), c.End(xlToRight)).Name = c.Value
Next

End Sub

[Sir Babydum GBE]

Killian

You're great.

I'd give you a million pounds if you were in the same office as me.

But you're not.

So I've given it to someone else now.

Sorry.

[Killian]

S'ok.

I'd only waste it on high living.

Who needs that?

[clvestin]

I sure would appreciate a small routine to remove illegal characters. I'm naming ranges from a title row, and I have things like() and alt-enter in the text.
I realize this post is from month ago, but

[Killian]

I realize this post is from month ago, but
by the wonders of the modern technology employed at VBAX, time now means nothing.

Speaking of technology employed at VBAX, the Knowledge Base has a couple of entries that might interest you: here (http://vbaexpress.com/kb/getarticle.php?kb_id=778) and here (http://vbaexpress.com/kb/getarticle.php?kb_id=68)

I had a think about it and I guess a simple way (if you like Regular Expressions as much as I don't) is to have an array of chars you don't want and use Mid to replace themFunction ReplaceIllegalChars(strText As String) As String

Dim arrIllegal()
Dim i As Integer

arrIllegal = Array("<", ">", "?", "[", "]", ":", "|", "*", "/", "\", "(", ")", "@")

For i = LBound(arrIllegal) To UBound(arrIllegal)
Do While InStr(strText, arrIllegal(i))
Mid(strText, InStr(strText, arrIllegal(i)), 1) = "_"
Loop
Next
ReplaceIllegalChars = strText

End Function

'use it like this
Sub test()

Dim mystring As String
Dim strResult As String

mystring = "V@V@#O*^#PG|GHG|\ib\87*&"

strResult = ReplaceIllegalChars(mystring)

End Sub

[mdmackillop]

You'll need to find some of the ascii numbers, but here's a start

Sub RemChars()
Dim Illegals As Variant, Cel As Variant, Char as variant
Illegals = Array("(", ")", ".", ",", Chr(32), Chr(160))
For Each Cel In Selection
For Each char In Illegals
Cel.Value = Application.WorksheetFunction.Substitute(Cel, char, "")
Next
Next
End Sub

[mbarron]

A quick way to assign names to ranges (as described in the original post) without the use of a macro (the illegal character would have to be remove of course) would be to:

Highlight your data - including the column you want to use as your names, press Ctrl + Shift +F3, choose "Left Column, and you're done.

[clvestin]

Thank You all

[mdmackillop]

Hi MBarron,
Welcome to VBAX
A very interesting shortcut which I have never come across. I must look into these further.
Using VBA, this creates the code

Sub CreateNames()
Selection.CurrentRegion.Select
Selection.CreateNames Left:=True
End Sub