Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel usedrange activate (2000)

    This has stumped me twice in the past three days, twice in the same project. There's no excuse for the second time.

    UsedRange seems to work only when the worksheet in question is active.
    Since migrating to Ranges, I tend to avoid "selection" and "activate" like the plague.
    The snippet below fails with "Run-time error '1004'" (Application-defined or Object-defined error).
    <pre>Dim rngIn As Range ' the range of rows to be processed
    Set rngIn = wks.UsedRange.Range(Cells(wks.UsedRange.Rows.Count , 1), Cells(1, 1))</pre>

    The modified version below works.
    <pre>wks.Activate
    Dim rngIn As Range ' the range of rows to be processed
    Set rngIn = wks.UsedRange.Range(Cells(wks.UsedRange.Rows.Count , 1), Cells(1, 1))</pre>


    The VBA Excel help files don't exactly say that the worksheet must be active for UsedRange to work ....
    UsedRange Property Example
    This example selects the used range on Sheet1.<pre>Worksheets("Sheet1").Activate
    ActiveSheet.UsedRange.Select</pre>



    I can't think why the worksheet, passed as an argument to UsedRange, would need to be active.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel usedrange activate (2000)

    It's because you use Cells without qualifying it. Therefore Cells is assumed to refer to the active worksheet, and this causes confusion. Try this:

    Set rngIn = wks.UsedRange.Range(wks.Cells(wks.UsedRange.Rows.C ount, 1), wks.Cells(1, 1))

    or perhaps

    Set rngIn = wks.UsedRange.Columns(1)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel usedrange activate (2000)

    Using the wks.cells technique, and disabling the wks.Activate, VB generates a 1004 error on the cl.Select.
    Reinstating the wks.Activate runs clean.
    <pre>' wks.Activate
    Dim rngIn As Range ' the range of rows to be processed
    Set rngIn = wks.UsedRange.Range(wks.Cells(wks.UsedRange.Rows.C ount, 1), wks.Cells(1, 1))
    '''
    ReDim typPerData(0) ' the array to hold our results
    '''
    Dim blnInLevel As Boolean ' TRUE if we are accumulating rows in a LEVEL
    '''
    Dim cl As Range
    For Each cl In rngIn.Cells
    cl.Select
    </pre>


    Using the wks.UsedRange.Columns(1) technique and disabling the wks.Activate, vb generates a 1004 error on the rngin.select.
    Reinstating the wks.Activate runs clean.

    <pre> wks.Activate
    Dim rngIn As Range ' the range of rows to be processed
    ' Set rngIn = wks.UsedRange.Range(wks.Cells(wks.UsedRange.Rows.C ount, 1), wks.Cells(1, 1))
    Set rngIn = wks.UsedRange.Columns(1)
    rngIn.Select</pre>


    Thanks for the suggestions.
    I may well have something else not quite right, but at this hour of a Saturday morning I'm going to run with the wks.Activate!

    I suspect that I don't understand Excel ranges as well as I ought.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel usedrange activate (2000)

    The Select method of a range is only valid on the active worksheet, so the error message is to be expected. I thought you were trying to avoid selecting cells? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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