Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    NE
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide All Rows with a 0 in 1 one column (200)

    I am looking for a way (probably a macro) to be able to hide all rows with a "0"in a particular column, especially if it can do more than one sheet at a time. If you do suggest a macro, please be specific, I don't have much experience with them. Thanks <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: Hide All Rows with a 0 in 1 one column (200)

    It is hard to be specific without knowing more about what you want to do, like the name of the worksheet and what column the zeros are in. However, the following VBA code should be close to what you need:

    <pre>Public Sub HideZero()
    Dim lLastRow As Long, I As Long
    lLastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Ro ws.Count, 1).End(xlUp).Row
    For I = lLastRow - 1 To 0 Step -1
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = 0 Then
    Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Hidden = True
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

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

    Re: Hide All Rows with a 0 in 1 one column (200)

    I just reread your message and saw that you wanted to do this for more than one sheet. If you want to do it on all sheets, then this code should be close:

    <pre>Public Sub HideZero()
    Dim lLastRow As Long, I As Long
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    lLastRow = oSheet.Cells(oSheet.Rows.Count, 1).End(xlUp).Row
    For I = lLastRow - 1 To 0 Step -1
    If oSheet.Range("A1").Offset(I, 0).Value = 0 Then
    oSheet.Range("A1").Offset(I, 0).EntireRow.Hidden = True
    End If
    Next I
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Location
    NE
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    I am sorry to be a pest, but I am really a newbie when it comes to code. My worksheet name is "CPP1Admin" and the zeros are in E:3 to E:592. I am not sure how to fit this into the code you gave me.

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    NE
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    What would that code look like with a range to find the zeros in as E3:E592? Thanks!

    Second question: Where do I put this code? Sorry, I know I am in over head now!

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hide All Rows with a 0 in 1 one column (200)

    sectheatre

    PMFJI

    I would suggest you start with the following:

    1) When in MS-Excel hold the Alt-Key and hit F11, the F11 is the key next to the F12 key on the top row of your keyboard just above the Backspace key.
    2) This will activate the Visual Basic Editor (VBE).
    3) In the VBE you will notice the project Window on the Left hand of your Window, if all is the way it should be.
    4) In that Project Window you will see a list of all open workbooks, and one of them should be your workbook you are working with... It will say something like VBAProject(xxxx) where xxxx is your workbook name. Maybe it would say something else, but if you would concentrate on the (xxxx) that would be OK.
    5) Highlight that workbook, and from the Insert Menu choose Module.
    6) Copy the code into that module. be very careful as to copy all of it.

    Now to answer your question about your range. I think if you look at Legare's code, in the second reply, he handles the cells in column A. Now if you want it to handle column E change the "A1" in the line:
    If oSheet.Range("A1").Offset(I, 0).Value = 0 Then to read:
    If oSheet.Range("E1").Offset(I, 0).Value = 0 Then and this should do it for Column E. The range E1:E2 <font color=red> MUST </font color=red> be full with something, even spaces, but it must not be blank. The Range E3:E592 will be taken care of by the line;
    lLastRow = oSheet.Cells(oSheet.Rows.Count, 1).End(xlUp).Row which will automaticaly get the number of rows that it needs to work with, if there are no blanks. If there are, well you are in trouble...

    The name of the worksheet will not matter since Legare's code is running on "For Each oSheet In Worksheets" all worksheets in the workbook.

    be careful when you copy the code into the module, you may want to print the reply message and make sure that all the lines read the same as in the print out.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    Read Wassim's explanation and then look at the attached; I remodeled Legare's code somewhat, but this should give you what you want. This code is set to only work on the sheets selected (do you know how to select multiple sheets?), there is an alternative line commented out to work on all sheets; the ' character in front of the line comments it out, just switch the ' to the second line if you want it to work on all sheets.

    ' For Each oSheet In Thisworkbook.WorkSheets 'use this line to process all sheets
    For Each oSheet In ActiveWindow.SelectedSheets 'use this line to process only selected sheets
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Nov 2001
    Location
    NE
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    Thanks everybody for your help. Unfortunatly, I think I am in over my head and shall resort to doing it by hand. Thank anyway. -Sarah

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    Sarah,

    If it's not too late try this <pre>Sub HideZero()
    Dim i As Integer
    Dim ws As Worksheet
    Dim oCell As Range
    Application.ScreenUpdating = False
    i = ActiveSheet.Index
    For Each ws In ActiveWorkbook.Sheets
    For Each oCell In ws.Range("E3:E592")
    If oCell.Value = 0 Then oCell.EntireRow.Hidden = True
    Next
    Next
    ActiveWorkbook.Sheets(i).Activate
    Application.ScreenUpdating = True
    End Sub</pre>

    That should hide all rin the given range if Column E = 0. It shouls work on all sheets.

    Andrew C

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    Why don't you try the autofilter function.

    menu - DATA>FILTER>Autofilter when your cursor is somewhere in the list.

    This will then place a 'little down arrow button' in the topmost row of each column in the list.

    Select the button for the column of interest, use the custom option and select 'does not equal' witht a value of zero and hit OK.

    This will hide the rows.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hide All Rows with a 0 in 1 one column (200)

    Thanks, I learned something!
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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