Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Clearing a Range of cells - size of Range not known

    I got some very useful help from this board some 6 months ago on a simple stock control sheet for Craft Fairs - I wanted a macro to colour in cells in each row dependent on the Quantity of each item available.

    I now need another favour please !

    In order to update the information the macro cleared the coloured cells first

    'Range("F2:V39").Select
    'Selection.Interior.ColorIndex = xlNone

    The problem is that we now need to increase the "V39" as the Number if items for sale or the maximum Quantity increases, and it would be best if the macro could do it for us! "F2" is not going to be a variable.

    I can't see how I can change the Range parameters from variables. It's easy enough to get the maximum counts for both row and column, but what do I do next ?

    Many thanks

    Rob

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by oldwood View Post
    I got some very useful help from this board some 6 months ago on a simple stock control sheet for Craft Fairs - I wanted a macro to colour in cells in each row dependent on the Quantity of each item available.

    I now need another favour please !

    In order to update the information the macro cleared the coloured cells first

    'Range("F2:V39").Select
    'Selection.Interior.ColorIndex = xlNone

    The problem is that we now need to increase the "V39" as the Number if items for sale or the maximum Quantity increases, and it would be best if the macro could do it for us! "F2" is not going to be a variable.

    I can't see how I can change the Range parameters from variables. It's easy enough to get the maximum counts for both row and column, but what do I do next ?

    Many thanks

    Rob
    Probably easier to use the syntax Range(Cells(row1, col1), Cells(row2, col2)) where (e.g.) F2 is represented by Cells(2,6)

  3. The Following User Says Thank You to jeremybarker For This Useful Post:

    oldwood (2012-06-26)

  4. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Many thanks JB - that the solution. I haven't solved the whole problem but it's got that one out of the way.

    Rob

Posting Permissions

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