Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping (XL97;SR2)

    When I run the following code the end result does not always place the "Grand Total" line as the last line of the data that is being grouped. There can be hundreds of blank lines between the last item grouped and the grand total line.



    Dim LastRow As Long
    With Worksheets("" & oSheet & "_Detail")
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    If LastRow > 1 Then
    With Range("A1:G" & LastRow & "")
    .Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("F1"), _
    Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End With
    .Outline.ShowLevels RowLevels:=2
    End If
    End With


    Thanks,
    John
    Attached Images Attached Images

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping (XL97;SR2)

    John

    I couldn't quite understand why you set LastRow as you did given you referenced from A1 and not UsedRange.Row. Surely
    LastRow = ActiveSheet.UsedRange.Rows.Count
    would have been enough - however, I suspect the rest ended up as 1-1 anyway. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Excel often loses track of where the real end is on a sheet.
    A surefire way to locate the last row is with code checking a specific column such as

    LastRow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row +1

    HTH

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping (XL97;SR2)

    UsedRange is the range that Excel thinks has been used. Unfortunately, there are many things that can cause Excel to think that a row is in the UsedRange even though it looks like there is nothing in the row. Some cell formatting can cause Excel to think that the row containing the cell with the formatting is being used, even though there is no data in the cell. Once this happens, the only way to get Excel to remove the row from the UsedRange is to delete the row and then save the workbook and close the workbook and reopen it. Therefore, using UsedRange to find the last row as you have done is not reliable.

    In your case, it looks like column B will always contain something in the cell in the last row. If this is correct, then you can use the following to get the last row:

    <pre> LastRow = Range("B65536").End(xlUp).Row
    </pre>


    If that is not the case, then you may need to start with the row you have calculated, and use a loop to search up from there and look at all of the cells in each row until you find a row where they are not all empty.
    Legare Coleman

Posting Permissions

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