Results 1 to 6 of 6
  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

    Understanding .Offset (Excel 2000 etc.)

    Apologies to those who suffered reading my post of 15 mins ago before I realised the error of my ways.

    The code below works, but it seems way too ugly for what I want. I'd appreciate understanding how I might better use the .Offset propoerty to obtain a range.
    My user has used part of a worksheet.
    I want to identify a single row immediately below the used area, as wide (in columns) as the used area.
    <pre>Public Function rngNextRow(wks As Worksheet) As Range
    ' Return the range of the useable area of the next available row
    Dim rng As Range
    Set rng = wks.UsedRange
    rng.Select
    Debug.Print
    Debug.Print rng.AddressLocal ' $A$1:$EC$8
    Set rng = rng.Offset(0, 0)
    Debug.Print rng.AddressLocal ' $A$1:$EC$8
    Set rng = rng.Offset(rng.Rows.Count, 0)
    Debug.Print rng.AddressLocal ' $A$9:$EC$16
    Debug.Print rng.Rows.Count, rng.Columns.Count ' 8 133
    Set rng = rng.Range(Cells(1, 1), Cells(1, rng.Columns.Count))
    Debug.Print rng.AddressLocal ' $A$9:$EC$9
    Set rngNextRow = rng
    'Sub TESTrngNextRow()
    ' rngNextRow(ActiveSheet).Select
    'End Sub
    End Function
    </pre>


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

    Re: Understanding .Offset (Excel 2000 etc.)

    You could use

    Dim rng As Range
    Set rng = wks.UsedRange
    Set rngNextRow = rng.Rows(rng.Rows.Count + 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: Understanding .Offset (Excel 2000 etc.)

    > You could use
    It's true, I could ..... and probably will! Thanks

    Further research (Google Groups "excel 2000 .UsedRange") reveals that I was battling a known bug in 2000/2002.
    I have a workaround that seems to fix my current problem by avoiding the empty cell in A1 of the worksheet:
    <pre> ''' Fix bug in 2000/2002
    If wks.Range("A1") = "" Then
    wks.Range("A1") = " "
    Else
    End If
    </pre>


  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Understanding .Offset (Excel 2000 etc.)

    UsedRange may include rows with non-default cell formats, but no data. To find the last row with data, use a variation on code in 508608, such as:

    rngNextRow = Activesheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Understanding .Offset (Excel 2000 etc.)

    > UsedRange may include rows with non-default cell formats, but no data.
    Thanks, but I'm pretty sure this was a reported bug.
    First call to the function delivers, say A1:K42.
    Next call to the function delivers B1:L42 - shifted one column to the right!

    I always thought of UsedRange as the internal equivalent of Ctrl-Home; Shift-Ctrl-End.
    Even if it isn't, Excel/VBA ought not to change its mind on a whim.

    My crude fix was the one shown above - make sure that there is something in the home cell at A1.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding .Offset (Excel 2000 etc.)

    It is NOT the same as control-home, control-shift-end.

    UsedRange may be C1020 ! In other words, the topleft cell isn't necessarily cell A1.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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