Macro to Hide Rows Based on "If" formula

[egghead]

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!

[HansV]

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.

[egghead]

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!

[HansV]

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

[egghead]

[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

[HansV]

You could change the macro as follows:

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:

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

[egghead]

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

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:

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!

[HansV]

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.

[HansV]

Here is the simplified version: [attachment=83981:Test2.xls]
The code has been reduced to the following event procedure in the Sheet2 module:

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

[egghead]

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?

[HansV]

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:

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

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

[egghead]

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

[HansV]

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.

[egghead]

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