• Creating a range name in VBA (Excel 2000 (9.0.2720))

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Creating a range name in VBA (Excel 2000 (9.0.2720))

    Author
    Topic
    #360812

    I have recorded a macro to create a range name labeled ‘MARDATA’. I want the range to be from ‘A6’ through to the end of my spreadsheet, the end position changing when I re-import data.

    Unfortunately it records the reference in an absolute manner. Can someone tweak / overhaul this code please…

    Sheets(“Before”).Select
    Range(“A6″).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Names.Add Name:=”MARDATA”, RefersToR1C1:= _
    “=Before!R6C1:R500C12”

    Viewing 0 reply threads
    Author
    Replies
    • #544150

      You could try
      Range(“A6″).CurrentRegion.Name=”MARDATA”
      if there are no blank rows or columns in your range.

      Graeme

      • #544161

        That is close, but I have a row of data in row5 which I don’t want as part of the range name.

        Incorporating

        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

        Is what I think I need

        • #544168

          try:-

          With ActiveWorkbook.Sheets("Before")
              .range(.Cells(6, 1), .Cells(6, 1).SpecialCells(xlLastCell)).Name = "MARDATA"
          End With
          
          

          That’s all you should need, no need to select sheets and ranges first.

          • #544172

            Exactly as ralphad says…
            Also remember that xlLastCell doesn’t always give the results you might expect – especially if your data occupies a smaller range after refresh.

            Graeme

            • #544178

              Ralphad,

              I copied your code exactly as you have it there, but it doesn’t seem to like the 1st ‘.cells’.

              Are all the dots, commas etc. exactly as would be required?

            • #544181

              The syntax is OK. I just tested it and it works fine.

              Are you sure you’ve isolated the error correctly?

            • #544182

              I could ramble here, but how about attaching your test file & I’ll copy it from there & see what happens – it’s probably something silly at my end.

            • #544184

              ok, file is attached…

              Note:
              1. Activesheet is Sheet1 – no need to select the ‘Before’ sheet before running code
              2. the range ‘MARDATA’ does not exist when you open the sheet
              3. just go into the VBA editor and run Sub Test()

            • #544188

              I added a SPACE & UNDERSCORE after the 1st line – oops!!

              Thanks Adam, it works a treat now.

    Viewing 0 reply threads
    Reply To: Creating a range name in VBA (Excel 2000 (9.0.2720))

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: