Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    egghead

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    egghead

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Should the code look only at Sheet2!D10 or at a range of cells (e.g. column D)?

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777086' date='26-May-2009 06:22']Should the code look only at Sheet2!D10 or at a range of cells (e.g. column D)?[/quote]

    Column D
    egghead

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    egghead

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is the simplified version: [attachment=83981:Test2.xls]
    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
    Attached Files Attached Files

  10. #10
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    egghead

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  12. #12
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    [quote name='HansV' post='777216' date='27-May-2009 06:58']Wouldn't that become irritating after a while? It'd mean you wouldn't return to the cell you last visited on Sheet3.[/quote]

    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.
    Attached Files Attached Files
    egghead

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  14. #14
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777235' date='27-May-2009 09:00']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.[/quote]

    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!
    egghead

Posting Permissions

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