Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005

    Unanswered: Setting a range in VBA that changes

    I have an Excel export out of Access that will always be 6 columns wide, but the number of rows will change with each export. I want to write the macro to set borders for that area, but am having a difficult time trying to figure out the code. I can have a cell do the math to tell me how many rows there are that need to have borders, but how can I add a statement that says I need a range starting at A2, that is 6 columns wide and covers rows = value of B2?

    I have looked thru help, but it escaped me.


  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Don't use a cell to calculate the range.
    Add a loop to your macro so that it goes down each cell in the first column until it hits an empty cell. Then subtract 1 from ActiveCell.Row, and use that value to calculate your range. If you use intRows as your variable, your command to set the range will be something like:
    Range("A2:F" & intRows).CODE

  3. #3
    Join Date
    Oct 2002
    Baghdad, Iraq
    The worksheet object has the UsedRange property that tells you how much of the worksheet is in use. ActiveSheet.UsedRange.Rows.Count would give you the number of rows used in the active worksheet, for example.

    In general, the UsedRange might include rows that only look empty. That shouldn't be an issue with data straight from Access.

  4. #4
    Join Date
    Dec 2003
    Ottawa, Canada

    will give you the number of rows then


    will select the set of data

    CurrentRegion is one of the most important property when you work with imported data from any database or accounting, finance, sales, production application
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

Posting Permissions

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