Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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"

  2. #2
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

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

    Graeme

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    try:-

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

    </pre>


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

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    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

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    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?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

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

    Are you sure you've isolated the error correctly?

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    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.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

    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()
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a range name in VBA (Excel 2000 (9.0.2720))

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

    Thanks Adam, it works a treat now.

Posting Permissions

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