CODEWITHSUNDEEP
excelvba
moreproblems
moreproblems

Reputation: 13

VBA Excel: Trying to name dynamic ranges organized into rows

I have data organized into rows and in column B I have data titles. I want to select the data after the titles and then give them range names based on that title. I was able to code a solution that could name column ranges dynamically this way, but when altering it to name the rows of data I run into a 1004 error, specifically at the rng.CreateNames point.

Sub RowNames()                                                                                     
Dim ws As Worksheet, firstCol As Long, lastCol As Long, rowNum As Long, r As Integer, n As Integer, rng As Range, rngName As Range
Set ws = ThisWorkbook.Sheets("MonthlySales")
Set rng = ws.Range("B2:N41")
    For n = 1 To rng.Rows.Count
        For r = rng.Columns.Count To 1 Step -1
            rowNum = rng.Rows(n).Row
            firstCol = rng.Columns(1).Column
            lastCol = rng.Columns(r).Column
                If Cells(firstCol, rowNum).Value <> "" Then
                    Set rngName = Range(Cells(firstCol, rowNum), Cells(lastCol, rowNum))
                        rngName.CreateNames Left:=True
                        Exit For
                End If
        Next r
    Next n

End Sub

Upvotes: 1

Views: 44

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Naming Row Ranges

Range.CreateNames Method

  • Frankly, never heard of it. Basically, in this case, you take a range and write different names in its first column and when you loop through the rows, for each row you write something like Range("A1:D1").CreateNames Left:=True to create a named range whose name is the value in A1 and it will refer to the range B1:D1.
  • To mix it up, this example (I think OP also) assumes that there might be blank cells in the first column, and the number of cells in each row range may vary. Each row range will be checked backwards for a value which will define its size.

The Code

Option Explicit

Sub RowNames()
    
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MonthlySales")
    ' Define Source Range.
    Dim rng As Range
    Set rng = ws.Range("B2:N41")
    ' Define Columns Count.
    Dim ColumnsCount As Long
    ColumnsCount = rng.Columns.Count
    
    Dim RowRange As Range ' Current Row Range
    Dim r As Long         ' Source Range Rows Counter
    Dim c As Long         ' Source Range / Current Row Range Columns Counter
    
    ' Loop through rows of Source Range.
    For r = 1 To rng.Rows.Count
        ' Create a reference to the current Row Range.
        Set RowRange = rng.Rows(r)
        ' Check if first cell of current Row Range contains a value,
        ' making it a possible candidate for a defined name.
        If RowRange.Cells(1).Value <> "" Then
            ' Loop through cells (columns) of current Row Range backwards.
            For c = ColumnsCount To 2 Step -1
                ' Check if current cell in current Row Range contains a value.
                If RowRange.Cells(c) <> "" Then
                    ' Create a named range from value in first cell. The range
                    ' is defined from the second cell to to current cell
                    ' in current Row Range.
                    RowRange.Cells(1).Resize(, c).CreateNames Left:=True
                    ' Exit loop, we got what we came for (the named range).
                    Exit For
                End If
            Next c
        End If
    Next r

End Sub

Upvotes: 1

Related Questions