A big hello to all experts in here! this is another one question that came up today. I have a dynamic list of names and I want to define a different name depending the first letter of the text in a cell. An example: If cells A1:A20 start with A then define>name : "Alpha". I found the following macro which works fine as fas as I have only one column. But I need to include more columns in each range. So, as usual please...
Sub Macro2()
Dim StartRow As Long, EndRow As Long, LastRow As Long, iROW As Long
Dim AscFirstChar As Integer
Dim Str4Name As String
Str4Name = "StartsWith_"
StartRow = 1
LastRow = Range("B65536").End(xlUp).Row
AscFirstChar = 65 'ASCII code for "A"
Cells(LastRow + 1, 2) = "1" 'Necessary to avoid error
Do While StartRow <= LastRow And AscFirstChar <= 90
If Asc(UCase(Left(Cells(StartRow, 2), 1))) = AscFirstChar Then
EndRow = StartRow
Do While Asc(UCase(Left(Cells(EndRow + 1, 2), 1))) = AscFirstChar
EndRow = EndRow + 1
Loop
ActiveWorkbook.Names.Add Name:=Str4Name & Chr(AscFirstChar), RefersToR1C1:= _
"=" & ActiveSheet.Name & "!" & Range(Cells(StartRow, 2), Cells(EndRow, 2)).Address(True, True, xlR1C1)
StartRow = EndRow + 1
AscFirstChar = AscFirstChar + 1
Else
AscFirstChar = AscFirstChar + 1
End If
Loop
Cells(LastRow + 1, 2).ClearContents
End Sub