1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to hide rows

Discussion in 'Business Applications' started by rflud, Jun 23, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. rflud

    rflud Thread Starter

    Joined:
    Jun 23, 2004
    Messages:
    3
    I have spreadsheet that has a bunch of parts listed in separate categories. Column A is the quantity column. I want to be able to hide all rows (using a macro) that have a zero in column A. The workbook has several sheets that need the same functionality. The macro must not hide rows with nothing in them or nothing in column A. For example a row may have content in other columns but nothing in column A because that row is not used for a specific part (therefore there will never be a quantity entered).

    The code below works great except that it hides all rows with that are zero or blank. I just need to figure out how to make it skip the blank rows (column A has no data). Any ideas? It doesn't have to be with my code just something that provides this function for all sheets in the workbook. Thanks.

    Option Explicit
    Sub HideRowsWithZeros()
    Dim ws As Worksheet
    Dim c As Range
    Dim rngRange As Range

    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets

    ws.Select
    Set rngRange = Range(Cells(1, 1), Cells(65336, 1).End(xlUp))

    For Each c In rngRange
    If c.Value = 0 Then
    c.EntireRow.Hidden = True
    End If
    Next c

    Next ws

    Application.ScreenUpdating = True

    End Sub
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  3. rflud

    rflud Thread Starter

    Joined:
    Jun 23, 2004
    Messages:
    3
    Here is how I got it to work. It may not be the best way but it's working - a little slow, but working.

    Option Explicit
    Sub HideRowsWithZeros()
    Dim ws As Worksheet
    Dim bytCol As Byte, lngRow As Long

    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets

    ws.Select
    For lngRow = 1 To 65536 'All Rows
    With ws
    For bytCol = 1 To 1 'Columns A
    If .Cells(lngRow, bytCol) <> "0" Then Exit For
    If bytCol = 1 Then .Rows(lngRow).Hidden = True
    Next bytCol
    End With
    Next lngRow

    Next ws

    Application.ScreenUpdating = True

    End Sub
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    How many rows are you using? You could probably change the range...
     
  5. rflud

    rflud Thread Starter

    Joined:
    Jun 23, 2004
    Messages:
    3
    Good point. I just changed it to 700. That should be fine for a while.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/242135

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice