• Macro to Hide Rows Based on “If” formula

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Macro to Hide Rows Based on “If” formula

    Author
    Topic
    #460112

    I have a formula on Worksheet 3 that looks at a specific cell on Worksheet 2.
    =IF(ISTEXT(Sheet2!G10),Sheet2!D10,””)

    On Worksheet 3, I want to hide any rows that have “” instead of text. Here’s my code so far:

    Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 4

    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = 0 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt
    End Sub

    The way my macro is written it hides all of the rows between 1 and 100 that don’t have the formula in it.

    Is there an easy fix? I just want my macro to look at the results of the formula and if the result is “” it should hide the row. If it places the cell value (D10), then the row should not be hidden.

    Thanks!

    Viewing 1 reply thread
    Author
    Replies
    • #1162272

      You could change the line

      If Cells(RowCnt, ChkCol).Value = 0 Then

      to

      If Cells(RowCnt, ChkCol).HasFormula And Cells(RowCnt, ChkCol).Value = 0 Then

      This adds the condition that Cells(RowCnt, ChkCol) has a formula.

    • #1162286

      That works great! Thanks!

      Do you have any suggestions for triggering the macro if Sheet 2 is modified? Maybe it could run if the file is saved or something like that?

      Thanks again!

      • #1162287

        Should the code look only at Sheet2!D10 or at a range of cells (e.g. column D)?

        • #1162288

          Should the code look only at Sheet2!D10 or at a range of cells (e.g. column D)?

          Column D

          • #1162289

            You could change the macro as follows:

            Code:
            Sub HURows()
            	BeginRow = 1
            	EndRow = 100
            	ChkCol = 4
            
            	For RowCnt = BeginRow To EndRow
            		If Worksheets("Sheet3").Cells(RowCnt, ChkCol).Value = 0 Then
            			Worksheets("Sheet3").Cells(RowCnt, ChkCol).EntireRow.Hidden = True
            		Else
            			Worksheets("Sheet3").Cells(RowCnt, ChkCol).EntireRow.Hidden = False
            		End If
            	Next RowCnt
            End Sub

            where Sheet3 is the name of the sheet with the formulas.

            Right-click the sheet tab of Sheet2 and select View Code from the popup menu.
            Copy the following code into the module:

            Code:
            Private Sub Worksheet_Change(ByVal Target As Range)
              If Not Intersect(Range("D:D"), Target) Is Nothing Then
            	Application.EnableEvents = False
            	Call HURows
            	Application.EnableEvents = True
              End If
            End Sub
            • #1162292

              I’m not having much luck at making the Sheet 2 macro work. I did goof up when I said the changes would be on Sheet 2, Column D. The changes are actually made to Column G (the formula displays text from Column D as a result). I tried changing the code to this (with no results):

              Code:
              Sub Worksheet_Change(ByVal Target As Range)
                If Not Intersect(Range("G:G"), Target) Is Nothing Then
              	Application.EnableEvents = False
              	Call HURows
              	Application.EnableEvents = True
                End If
              End Sub

              If I change a value in Column G and then manually run the HURows macro, it will unhide and hide. I have the HURows macro in Module2 and the Worksheet Change macro in Sheet 2. Here’s the code for HURows now:

              Code:
              HURows()
              	BeginRow = 1
              	EndRow = 100
              	ChkCol = 4
              
              	For RowCnt = BeginRow To EndRow
              		If Worksheets("Sheet3").Cells(RowCnt, ChkCol).HasFormula And Cells(RowCnt, ChkCol).Value = "" Then
              			Worksheets("Sheet3").Cells(RowCnt, ChkCol).EntireRow.Hidden = True
              		Else
              			Worksheets("Sheet3").Cells(RowCnt, ChkCol).EntireRow.Hidden = False
              		End If
              	Next RowCnt
              Range("A1").Select
              End Sub

              Any ideas or suggestions? Thanks!

            • #1162313

              You have to specify Worksheets(“Sheet3”) for all occurrences of Cells(…) in the HURows macro.

              See the attached sample workbook.

              BTW if there is a one-to-one relationship between the rows in Sheet2 and those in Sheet3, as in the sample workbook, the code could be simplified considerably.

            • #1162317

              Here is the simplified version:
              The code has been reduced to the following event procedure in the Sheet2 module:

              Code:
              Sub Worksheet_Change(ByVal Target As Range)
                Const ChkCol = 4
                Dim oCell As Range
                If Not Intersect(Range("G:G"), Target) Is Nothing Then
              	Application.EnableEvents = False
              	For Each oCell In Intersect(Range("G:G"), Target).Cells
              	  With Worksheets("Sheet3").Cells(oCell.Row, ChkCol)
              		.EntireRow.Hidden = .HasFormula And .Value = ""
              	  End With
              	Next oCell
              	Application.EnableEvents = True
                End If
              End Sub
            • #1162404

              Thanks so much – that works really well! And I like your sample data “ha!”

              Another wish: I had added Range(“A1”).Select in my Sheet 3 macro. Can I insert that into the Sheet2 macro somewhere so that when I click to Sheet 3 it’s always in A1?

            • #1162405

              Wouldn’t that become irritating after a while? It’d mean you wouldn’t return to the cell you last visited on Sheet3.

              If you really want this:
              – Right-click the sheet tab of Sheet3.
              – Select “View Code” from the popup menu.
              – Copy/paste the following code into the worksheet module:

              Code:
              Private Sub Worksheet_Activate()
                Range("A1").Select
              End Sub

              Whenever you switch to Sheet3, Excel will select cell A1.

            • #1162423

              Wouldn’t that become irritating after a while? It’d mean you wouldn’t return to the cell you last visited on Sheet3.

              That’s a very good point! I’ll wait and see if it becomes an issue.

              I have been playing with the “test” file and it works great, too. My file will not have matching row numbers, so I’m thinking that the code is that file is the way to go. However, I still cannot get the code on Sheet2 to work on my file.

              I’m going to attach a sample of the file and maybe there is something simple I’m missing.

              Thank you for taking a look at it!

              PS – the Sheet2 code seems to work if I add text; it will unhide the row; but if I delete text it doesn’t hide the row.

            • #1162425

              If I enter something in a cell such as G14 on Sheet2, the corresponding row on Sheet3 is unhidden, and if I clear G14, the corresponding row on Sheet3 is hidden, so it works OK for me.

              If you want to use the HURows macro, you should change

              If Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).HasFormula And Cells(RowCnt, ChkCol).Value = “” Then

              to

              If Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).HasFormula And Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).Value = “” Then

              as mentioned higher up in this thread.

            • #1162441

              If you want to use the HURows macro, you should change

              If Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).HasFormula And Cells(RowCnt, ChkCol).Value = “” Then

              to

              If Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).HasFormula And Worksheets(“Sheet3”).Cells(RowCnt, ChkCol).Value = “” Then

              as mentioned higher up in this thread.

              That was what I needed! I thought I’d done that, so thank you for catching it for me. I’m so appreciative! I have learned a lot. Thank you!

    Viewing 1 reply thread
    Reply To: Macro to Hide Rows Based on “If” formula

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: