Hide rows based on multiple cell content in row

[tjktm]

<style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-536870145 1073786111 1 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:8.0pt; margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:8.0pt; line-height:107%;} @Page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style> I need help to hide/unhide rows based on the contents of two of the cells in the same row. My current code doesnt quite do the trick anymore due to having to insert or delete rows:


If Column A has specific data (ex. S.2) and Column L in that row has no dollar amount, I would need to hide the column. If Column L does have a dollar value, then it remains unhidden.



Below is an example of what I have currently. However, the number of rows change (+/-) so I cannot use the code for specific cell values as shown below:
Sub Hide_Unhide_0300()

For Each cell In Range("L158:L183")

If cell.EntireRow.Hidden = True Then

cell.EntireRow.Hidden = False
Else
If cell.Value = "0" Then
cell.EntireRow.Hidden = True
End If
End If
Next cell

End Sub

Thank you for any help you can give

TJ

 

[jeffreybrown]

Have you tested the macro in post # 4 with the sample data in post # 6?

Does the macro run fine or as expected?

I ran the code posted in # 4 against your small sample data in # 3 and everything after row 3 was hidden as you desire.

Your data needs scrubbed for kinks.

 

[tjktm]

yes i have several merged cells. I would be glad to share the file with you privately through a dropbox link but won't be able to do that until tomorrow. If that is acceptable.

 

[Fluff]

The merged cells are almost certainly what is probably causing the problem. Merged cells are a nightmare & best avoided.

I would be glad to share the file with you privately through a dropbox link

Files should not be shared privately, as this is against board policy, but you can post a link to the thread. That way all members can have a look & offer help.

 

[tjktm]

Sorry, I was not aware of that policy. I will need to strip the file of material that i do not wish to share publicly first prior to posting a link. I will try to have this done tomorrow.

Thanks again

 

[tjktm]

Fluff,

below is a dropbox link to the file. I'm hopeful that you or someone else will be able to help me find the problem.

https://www.dropbox.com/s/dtfi5ovee0ze0sh/Test Template 2.3.xltm?dl=0

Thanks

 

[Rick Rothstein]

Fluff,

below is a dropbox link to the file. I'm hopeful that you or someone else will be able to help me find the problem.

https://www.dropbox.com/s/dtfi5ovee0ze0sh/Test Template 2.3.xltm?dl=0

Thanks

In Fluff's macro, change the A2 to A10 in order to get past your merged cells. Also, add...

Application.ScreenUpdating = False

at the beginning of the macro (you are already setting it to True at the end. Here is what the revised code should look like...

[table="width: 500"]
[tr]
	[td]Sub Hide_Unhide_0300()

   Dim Cl As Range
 
   Application.ScreenUpdating = False

   Cells.EntireRow.Hidden = False
   
   For Each Cl In Range("A10", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value = "S.3" And Cl.Offset(, 11).Value = 0 Then
         Cl.EntireRow.Hidden = True
      ElseIf Not Cl.Value = "S.3" Then
         Cl.EntireRow.Hidden = True
      End If
   Next Cl

   Application.ScreenUpdating = True

End Sub[/td]
[/tr]
[/table]

By the way, this code can be "simplified" somewhat...

[table="width: 500"]
[tr]
	[td]Sub Hide_Unhide_0300()

   Dim Cl As Range
 
   Application.ScreenUpdating = False

   Cells.EntireRow.Hidden = False
   
   For Each Cl In Range("A10", Range("A" & Rows.Count).End(xlUp))
      Cl.EntireRow.Hidden = Not (Cl.Value = "S.3" And Cl.Offset(, 11).Value > 0)
   Next Cl

   Application.ScreenUpdating = True

End Sub[/td]
[/tr]
[/table]

 

[tjktm]

Thanks. that is definitely a step in the right direction. is there a way to get it to only work with the Range containing "S.3"? As you can see in the worksheet, there are 16+ categories and i will need to make this work for each. ie. S.1 , S.2, etc. . it currently hides everything in the entire worksheet, i only need it to work with the items containing " S.3 " in Column A

 

[Fluff]

Using Rick's simplified code how about

Sub Hide_Unhide_0300()
Application.ScreenUpdating = False
   Dim Cl As Range
 
   Application.ScreenUpdating = False

   Cells.EntireRow.Hidden = False
   
   For Each Cl In Range("A10", Range("A" & Rows.Count).End(xlUp))
      Cl.EntireRow.Hidden = Not (Cl.Value Like "S.*" And Cl.Offset(, 11).Value > 0)
   Next Cl

   Application.ScreenUpdating = True

End Sub

 

[tjktm]

it is still incorporating almost all the other rows with an "S " in column A and hiding those rows.

 

[Fluff]

When I run it on your test file I get

Excel 2013 32 bit
	A	B	C	D	E	F	G	L
9		R.1	GENERAL CONDITIONS	$1,906.78				$1,906.78
25	S.1	01.300	BUILDER'S RISK		1	$6.78	$6.78	$6.78
45	S.1	01.010	General Conditions	Pro-Core($150)	1	$150.00	$150.00	$150.00
46	S.1	01.010	General Conditions	Super Bonus	1	$250.00	$250.00	$250.00
69	S.1	01.060	Power Wash		1	$1,500	$1,500.00	$1,500.00
99	S.2	02.125	Traffic Control		1	20	$20.00	$20.00
166	S.3	03.271	Casting Bed		1	10	$10.00	$10.00
167	S.3	03.271	Panelbooks		2	20	$40.00	$40.00
168	S.3	03.411	Paving Materials		3	30	$90.00	$90.00
Cost Estimate

Along with project totals etc underneath.
Is this not what you want?