Results 1 to 3 of 3

Thread: Solved: create a named range with a changing area

  1. #1

    Solved: create a named range with a changing area

    EL XID created the code below which creates a dynamic range when the cell value in column "A" changes which works a treat. This does exactly as expected and I want to keep this intact. However you will see that it looks at the value in "a" and then tags either a _c or a _d depending on the column.

    The new bit I am look for is to select the range in column B to F using the same split as listed above and name it as column "A" _r . The aim is to use this range in a vlookup

    [VBA]Public Sub ProcessData()
    Dim i As Long
    Dim LastRow As Long
    Dim StartLine As Long
    Dim RangeName As String

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    StartLine = 1
    RangeName = Replace(.Cells(1, "A").Value, " ", "_")
    For i = 2 To LastRow + 1

    If Replace(.Cells(i, "A").Value, " ", "_") <> RangeName Then

    .Cells(StartLine, "A").Offset(0, 1).Resize(i - StartLine).Name = RangeName & "_C"
    .Cells(StartLine, "A").Offset(0, 2).Resize(i - StartLine).Name = RangeName & "_D"
    RangeName = Replace(.Cells(i, "A").Value, " ", "_")
    StartLine = i
    End If
    Next i
    End With
    End Sub
    Thanks in advance


  2. #2
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    May 2004
    Add this line
    .Cells(StartLine, "A").Offset(0, 1).Resize(i - StartLine, 5).Name = RangeName & "_r"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Thanks mdmackillop...

    That was perfect. It seems so easy when you see the answer.

    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts