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.

Solved: Macro to hide all rows whose columns have zeros

Discussion in 'Business Applications' started by PincivMa, Aug 2, 2014.

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

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Again

    CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

    Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.

    Mario
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Try this ...

    Code:
    Sub HideRows()
    Dim lRow As Double
    Dim lcol As Double
    Dim i As Integer
    Dim i2 As Integer
    Dim CN As Double
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = lRow To 1 Step -1
        For i2 = 1 To lcol
            If Cells(i, i2) = 0 Then
                CN = CN + 1
            End If
        Next
            If CN = lcol Then
                Rows(i).Hidden = True
            End If
        CN = 0
    Next
    End Sub
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Maybe I am not seeing something, but if you delete a row that has a 0 in Column D, are you nor loosing data that is in Column A?
     
  4. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Xcubed

    Thanks for the macro. It works great. I managed to use your macro to also unhide columns with all zeros. I have a question for you. I understood most of the code but I did not understand "CN", since that variable was not defined as the other variables were. What does "i2" mean . Also, I'm not sure what Keebellah is saying since no columns were deleted when I ran the macro.

    Thanks again.

    Mario
     
  5. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    These are the variables that have been declared including CN


    Dim lRow As Double
    Dim lcol As Double
    Dim i As Integer
    Dim i2 As Integer
    Dim CN As Double


    lRow and lCol define the limits of your data i.e. last row and last column
    i and i2 are the counters that will loop through each row and, within that, each column
    CN is a counter that keeps track of the number of zeroes in a row. If CN = lcol then the row contains all zeroes and will be hidden.


    I think keebellah was not clear (I was confused as well) on your requirements. Your references to rows and columns was confusing as was the relationship of this post to the one which codelexicon resolved where a column was hidden. I just made some assumptions which worked out well (this time)
     
  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/1130823

  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