Results 1 to 3 of 3

Thread: Auto sort and hide rows with zero quantity
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Auto sort and hide rows with zero quantity


    I have data pulling into a worksheet from a previous worksheet. There is data populating rows 3-107 and columns A-I.

    I want to sort the whole table by the values in column G in descending order, then hide any rows that contain a zero value in the G column.

    Is there a way to do this automatically? I know how to do this manually, but I want the workbook user(s) to get the information automatically.

    Thank you in advance for any guidance you are able to provide.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Post Thanks / Like
    15 Post(s)
    1 Thread(s)

    Default Re: Auto sort and hide rows with zero quantity

    you can try PowerQuery (Get&Transform)

    select your table (Ctrl+T) - choose with/without headers
    from Data - select From Table
    it will open PowerQuery Editor with your table
    select your column
    filter by 0
    sort descending
    Close&Load (to the sheet)
    after that you can use Ctrl+Alt+F5 to refresh QueryTable if any new data will be added into the source table
    I know you know but I forgot my Crystal Ball and don't know what you know

    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Post Thanks / Like
    35 Post(s)
    8 Thread(s)

    Default Re: Auto sort and hide rows with zero quantity

    Welcome to the Forum!

    This macro will do what you requested.
    Sub mamapear()
    Dim R As Range, c As Range
    Set R = Range("A3").CurrentRegion
    Application.ScreenUpdating = False
    R.Sort key1:=[G3], order1:=xlDescending
    For Each c In R.Columns(7).Cells
        If c.Value = 0 Then c.EntireRow.Hidden = True
    Next c
    Application.ScreenUpdating = True
    End Sub

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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