For loop to uniquely name each non empty cell in a range

Question

The code below names the last cell in the range as opposed to each cell in the range.

I am trying to run this loop so that starting from cell A1, any non empty cells are named "Guidance1", "Guidance2", and so on.

Sub GiveAllCellsNames()

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim R As Range
    Dim NameX As String

    Static I As Long
    I = I + 1
 
    NameX = "Guidance" & I

    For Each R In Range("A1:A390").Cells
        If R.Value <> "" Then
            With R
                wb.Names.Add NameX, RefersTo:=R
            End With
        End If
    Next R

End Sub

I tried this loop without the "with statement" on the "R" range variable and get the same result.

You're not updating `NameX` inside the loop - you can't give all the cells the same name. — Tim Williams, Oct 25 '22 at 21:28
@TimWilliams. Any advice on how to update NameX within the loop? Is it a matter of just declaring the variables within the loop instead? — Kevin Perez, Oct 25 '22 at 21:56
Is `ActiveWorkbook` the workbook containing this code? What is the name of the worksheet where these named cells are located? — VBasic2008, Oct 25 '22 at 23:16

TinMan · Accepted Answer · 2022-10-25T22:41:03.070

0

A named range can be added using the Range object's name property.

Change

 wb.Names.Add NameX, RefersTo:=R

To

 R.Name = NameX

I need to be incremented and the name should be updated inside the loop.

Sub GiveAllCellsNames()

    Dim wb As Workbook

    Set wb = ActiveWorkbook

    Dim R As Range

    Dim NameX As String

    Static I As Long

    For Each R In Range("A1:A390").Cells

        If R.Value <> "" Then
        
            I = I + 1
            NameX = "Guidance" & I
            
            With R

                wb.Names.Add NameX, RefersTo:=R

            End With

        End If

    Next R

End Sub

edited Oct 25 '22 at 22:41

answered Oct 25 '22 at 21:28

TinMan

6,624
2
10
20

Unfortunately, that yielded the same result. I appreciate the attempt though. – Kevin Perez Oct 25 '22 at 21:58
@KevinPerez Sorry about that. I updated my answer. Are you sure that you want `I` to be a static varaible? – TinMan Oct 25 '22 at 22:42
This worked perfectly. Yes, the intent was a static incrementing # so that each name is unique for each non blank cell in the range. I appreciate your help as I now know to make such a change within the loop for future reference. Thank you! – Kevin Perez Oct 26 '22 at 02:35

score 0 · Answer 2 · answered Oct 26 '22 at 02:23

Name Non-Blank Cells

Sub NameAllCells()

    Const BeginsWithString As String = "Guidance"

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1:A390")
    Dim Data() As Variant: Data = rg.Value
    
    DeleteNamesBeginsWith wb, BeginsWithString
    
    Dim r As Long
    Dim n As Long
    
    For r = 1 To UBound(Data, 1)
        If Len(CStr(Data(r, 1))) > 0 Then
            n = n + 1
            wb.Names.Add BeginsWithString & n, rg.Cells(r)
        End If
    Next r

End Sub

Sub DeleteNamesBeginsWith( _
        ByVal wb As Workbook, _
        ByVal BeginsWithString As String)
    
    Dim nmLen As Long: nmLen = Len(BeginsWithString)
    
    Dim nm As Name
    
    For Each nm In wb.Names
        If InStr(1, nm.Name, BeginsWithString, vbTextCompare) = 1 Then nm.Delete
    Next nm
    
End Sub

For loop to uniquely name each non empty cell in a range

2 Answers2

Name Non-Blank Cells