Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

  1. #1
    Join Date
    14th August 2006
    Posts
    34

    Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    Hi,


    I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.

    Thanks,
    Chock.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,186

    Re: Empty rows should be hided through code !

    I think you will need to add a bit more information
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    20th April 2005
    Location
    Morgan Hill, CA, USA
    Posts
    37

    Re: Empty rows should be hided through code !

    Chock, I don't fully understand what is happening. How is the row being examined for being empty or non-empty? Do you just look at it? If so, I assume you would like to click on the empty row to select it then press a "Hide Row" button.
    Same for the unhide process, except you can't click on a hidden row. To unhide a row manually, you have to select the visible rows above and below the hidden row.
    If you want to use VBA to examine the input rows,then code can be written to select the rows of interest and hide or unhide as is appropriate.
    I'm not sure how you receive the sheet. Do you get a printed version or is the sheet transmitted to you electronically in some fashion?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Empty rows should be hided through code !

    Taking a punt

    VB:
     
    Public Sub HideRows() 
        Dim iLastRow As Long 
        Dim i As Long 
         
        With ActiveSheet 
             
            iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
            For i = 1 To iLastRow 
                Rows(i).Hidden = Application.CountA(Rows(i)) = 0 
            Next i 
             
        End With 
         
    End Sub 
    
    
    HTH

    Bob

  5. #5
    Join Date
    14th August 2006
    Posts
    34

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    Hi,

    Thanks for your replies.

    royUK - Actually now i have two XL Files Assume 1 file named Division1.Xls
    and the other file Named Department.Xls.

    The Department.Xls has 2 sheets Sheet1 and Sheet2. The Sheet1 is linked with the Division1.Xls sheet and displaying all the values.

    There are some rows has now values in Division1.Xls. We know the same will be in Department.Xls. I need to hide the empty rows in the Department.Xls Sheet1.

    So i have to check the cells A,B,C,D if all the 4 cells don't have values then that row should be hided. This is my criteria. And also one thing i have to mention , i have to start this from 10th row and end it at the row number 100. Because The first 10 rows contains headings and titles. Hope i given detailed information.


    Cyberdude - The row should be examined by checking the cells A,B,C,D if all 4 is empty then the row should be hided.
    Yes i know to hide the rows manually, but the thing is i am coding the sheets
    and changing the color based on the date column. just a piece of code is below

    VB:
    If Worksheets("GPED PROBLEM WELLS").Cells(IncCnt, 3).Value = strn1 And strn1Bo1 = True Then 
        For IncCnta = 1 To 30 
            If strn1Bo1 = True Then 
                If Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn2 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn3 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn4 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn5 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn6 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn6 Or Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3) = strn7 Then 
                    IncCnt = IncCnta 
                    IncCntb = IncCnta 
                    Exit For 
                End If 
                If IsDate(Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value) = True Then 
                    If Date - Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value >= 100 Then 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3).Font.Color = 255 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 4).Font.Color = 255 
                    ElseIf (Date - Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value >= 70) And (Date - Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value <= 99) Then 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3).Font.Color = vbBlue 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 4).Font.Color = vbBlue 
                    ElseIf Date - Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value <= 69 Then 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3).Font.Color = 25350 
                        Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 4).Font.Color = 25350 
                    End If 
                ElseIf IsDate(Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 8).Value) = False Then 
                    Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 3).Font.Color = vbBlack 
                    Worksheets("GPED PROBLEM WELLS").Cells(IncCnta, 4).Font.Color = vbBlack 
                End If 
            End If 
        Next 
    End If 
    
    
    So i need to hide the rows in code after check the A,B,C,D is empty or not & vice versa (to unhide the rows). so i need a syntax help or example for that.
    Actually the Division.Xls & Department.Xls are all in the same server in same directory.



    Bob Phillips - I tried with your code. When it comes for the first time inside the For Loop it hides the first row because its empty and it goes to the Worksheet_Calculate event. I placed a subroution to changed colors of rows in WorkSheet_Calculate event and Worksheet_Change event. Inside that event i place your subroutine HideRows. So its rounding again and again and the system get hangs. Kindly tell me the code to check 4 Cells A,B,C & D in each row is empty and hide the rows through code. and if its not empty then it should be unhide.

    Thanks,
    Chock.
    Last edited by itchock; September 5th, 2006 at 16:03. Reason: corrected the coded

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    VB:
     
    Public Sub HideRows() 
        Dim iLastRow As Long 
        Dim i As Long 
         
        With ActiveSheet 
             
            iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
            For i = 1 To iLastRow 
                Rows(i).Hidden = Application.CountA(Cells(i, "A").Resize(, 4)) = 0 
            Next i 
             
        End With 
         
    End Sub 
    
    
    HTH

    Bob

  7. #7
    Join Date
    14th August 2006
    Posts
    34

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    Hi Bob,

    Your code only hides the first row. Kindly tell me how to hide rows from 10 to 100 only if the Cells A, B, C & D are empty.

    VB:
    Sub HideRows() 
        Dim i As Integer 
        For i = 10 To 100 
            If Worksheets("Sheet1").Cells(i, 1).Value = "" And Worksheets("Sheet1").Cells(i, 2).Value = "" And Worksheets("Sheet1").Cells(i, 3).Value = "" And Worksheets("Sheet1").Cells(i, 4).Value = "" Then 
                Worksheets("Sheet1").Rows(i).Hidden = True 
            End If 
        Next 
    End Sub 
    
    
    Will the above code will solve the issue. But your code seams advanced. Can u explain it how it will suit for this scenario.

    Thanks,
    Chock.
    Last edited by itchock; September 6th, 2006 at 20:07.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    Try either of 2 macros below. The second is the fastest
    VB:
    Sub HideRows() 
        Dim rRange As Range, rCell As Range 
        Dim strVal As String 
         
        Set rRange = Worksheets(1).Range("A1:A100") 
         
        For Each rCell In rRange 
            strVal = rCell & rCell(1, 2) & rCell(1, 3) & rCell(1, 4) 
            rCell.EntireRow.Hidden = strVal = vbNullString 
        Next rCell 
         
         'A, B, C & D are empty.
         
    End Sub 
     
    Sub HideRows2() 
        Dim rRange As Range, rFormulas As Range 
        Dim strVal As String 
         
        Set rRange = Worksheets(1).Range("A1:A100") 
        rRange.Offset(0, 5).EntireColumn.Insert 
        Set rFormulas = rRange.Offset(0, 4) 
        With rFormulas 
            .Value = "=IF(COUNTA(RC[-4]:RC[-1]),"""",1)" 
            .Value = .Value 
            .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete 
            .EntireColumn.Delete 
        End With 
         
         'A, B, C & D are empty.
         
    End Sub 
    
    
    Last edited by Dave Hawley; September 7th, 2006 at 11:15.

  9. #9
    Join Date
    14th August 2006
    Posts
    34

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    Hi Dave Hawley,


    Thanks for the reply, I am using the below code now its working fine. But the problem is it should be fast as you mentioned, its takes more time now to process my other codes. You given 2 subroutines and said the second one is the fastest. But its very hard to understand while the first one is easy so shall i use your first subroutine for the timing until i understand your 2nd subroutine clearly (offset, CountA, RC i din't used it before moreover you have used the delete statement, actually i don't wan't to delete any rows, just need to be hided), or should i use what i given below. Kindly tell me which one will be fastest.

    VB:
    Sub HideRows() 
         
        Dim IncVal As Integer 
        For IncVal = 10 To 100 
            If Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 3).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 4).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 5).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 6).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 7).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 8).Value = "" And Worksheets("GPED PROBLEM WELLS").Cells(IncVal, 9).Value = "" Then 
                If Worksheets("GPED PROBLEM WELLS").Rows(IncVal).Hidden = False Then 
                    Worksheets("GPED PROBLEM WELLS").Rows(IncVal).Hidden = True 
                End If 
            Else 
                If Worksheets("GPED PROBLEM WELLS").Rows(IncVal).Hidden = True Then 
                    Worksheets("GPED PROBLEM WELLS").Rows(IncVal).Hidden = False 
                End If 
            End If 
        Next 
         
    End Sub 
    
    

    Thankyou,
    Chock.
    Last edited by itchock; September 7th, 2006 at 15:41. Reason: Added a clear description about the issue.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

    That's not either of my 2 code examples, not even close.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Hide Blank Rows & Those With "" Empty Text
    By jamesparker_1 in forum EXCEL HELP
    Replies: 1
    Last Post: May 21st, 2007, 22:40
  2. Loop Until Two Empty Or Blank Rows
    By jochancs in forum EXCEL HELP
    Replies: 1
    Last Post: February 24th, 2007, 01:44
  3. Hide all rows when empty - not working
    By GuyGadois in forum EXCEL HELP
    Replies: 2
    Last Post: January 25th, 2006, 17:44
  4. Hide Rows if Column D is empty
    By Mike M in forum EXCEL HELP
    Replies: 10
    Last Post: May 24th, 2005, 16:46

Bookmarks

Posting Permissions

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