Results 1 to 4 of 4

Thread: Outline By Customer Name

  1. #1
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13

    Outline By Customer Name

    I have the following code that I have previously used to outline data based on "" values. I was lucky enough to search a forum and modify the code a little bit to get it to work for my needs. However, I am unsure that I understand the code. I thought I could simply change a few numbers to make it work but, I am having trouble. The code is as follows:

    Code:
    Sub GroupData1()
    Dim i As Integer, LastRow As Integer
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To LastRow
    
        If Not Left(Cells(i, 2), 3) = "" Then
            Cells(i, 2).EntireRow.Group
        End If
    
    Next i
    End Sub
    I have attached a file that has a data sample. I am trying to outline the rows below "" in column E. This way the customer name would be the only row showing when the data is collapsed. Is there an easy way to modify the above code to meet my needs? In addition I would like to try to understand the code more thoroughly. Could anyone add comments to the code in order to help me understand it in full

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 11-30-2023 at 02:45 PM.
    Using Excel 2010

  2. #2
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    I tried to use this code which I got help with a similar issue on before I posted:

    I believe ExcelFox was my hero in this case:

    Code:
    Sub Consolidator()
    
        Dim obj As Range
        
        'Since there is a possibility that there are already Outlines made in the used range, we'd want to ensure it is removed
        On Error Resume Next
        Do Until Err.Number <> 0
            Worksheets("Sheet1").UsedRange.Rows.Ungroup 'This is where we remove the outlining
        Loop
        Err.Clear: On Error GoTo 0: On Error GoTo -1 'Clear up and reset the error handling
        For Each obj In Worksheets("Sheet1").UsedRange.Columns(5).Cells.SpecialCells(xlCellTypeBlanks) 'We use the specialcells method and pick only the blank cells, and then we loop through each blank cell
            If Not IsEmpty(obj.Offset(2)) Then 'If the cell 2 rows below the blank cell is not empty, then that means there are at least 2 rows to be grouped, in which case End(xlDown) will take use to the last row for that section
                obj.Parent.Range(obj.Offset(1), obj.Offset(1).End(xlDown)).Rows.Group ' so the section of the range that we want to group starts from the first cell below the blank cell, all the way down to the last cell in that group before the next blank, which can be located using End(xlDown) because there are at least 2 rows
            Else
                obj.Offset(1).Rows.Group 'If the cell 2 rows below the blank cell is empty, we can assume that the section to be grouped has only 1 row, so we just group that row
            End If
    
        Next obj
        
    End Sub
    Using Excel 2010

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    After reading through the notes on Rick's code thoroughly and trying to manipulate it, I have again come up short. I can't seem to get the spreadsheet to cooperate. I have searched a few different forums but, no dice. I am stumped.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?p=296754#p296754
    https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
    https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
    https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
    https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-28-2024 at 02:17 PM.
    Using Excel 2010

  4. #4
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    As it turns out there was some kind of strange formatting on the file. It did not register the cells that I was visually seeing as blank to be blank (I hope this makes sense). I ended up copying the data to another sheet and restoring a format that would read blank cells.... This is the resulting code that works:

    Code:
    Sub Part1of4()
      Columns("A:E").Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End Sub
    Sub Part2of4()
    Dim i As Integer, LastRow As Integer
    
    'Group Cells in between blanks in column 5
    
    LastRow = Cells(Rows.Count, 5).End(xlUp).row
    
    For i = 2 To LastRow
    
        If Not Left(Cells(i, 5), 3) = "" Then
            Cells(i, 5).EntireRow.Group
        End If
    
    Next i
    End Sub
    Sub Part3of4()
    '
    ' AlignUserGroupButtons Macro
    
        With ActiveSheet.Outline
            .AutomaticStyles = False
            .SummaryRow = xlAbove
            .SummaryColumn = xlLeft
        End With
    End Sub
    Sub Part4of4()
    'Collapses all Groups
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    End Sub
    
    Sub RunSalesByCustomer()
    Call Part1of4
    Call Part2of4
    Call Part3of4
    Call Part4of4
    End Sub
    Using Excel 2010

Tags for this Thread

Posting Permissions

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