Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Empty rows should be hided through code !

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

    Roy

    New users should read the Forum Rules before posting

    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.

    Comment


    • #3
      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?

      Comment


      • #4
        Re: Empty rows should be hided through code !

        Taking a punt

        Code:
        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

        Comment


        • #5
          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

          Code:
           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, 16:03. Reason: corrected the coded

          Comment


          • #6
            Re: Hide Blank/Empty Rows &amp; Shown/Unhide Non Empty Ones

            Code:
            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

            Comment


            • #7
              Re: Hide Blank/Empty Rows &amp; 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.

              Code:
              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, 20:07.

              Comment


              • #8
                Re: Hide Blank/Empty Rows &amp; Shown/Unhide Non Empty Ones

                Try either of 2 macros below. The second is the fastest
                Code:
                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, 11:15.

                Comment


                • #9
                  Re: Hide Blank/Empty Rows &amp; 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.

                  Code:
                  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, 15:41. Reason: Added a clear description about the issue.

                  Comment


                  • #10
                    Re: Hide Blank/Empty Rows &amp; Shown/Unhide Non Empty Ones

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

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X