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]

You reference L158:L183, but what is the range you need to evaluate. All of column A because we can't use L?

Sub Hide_Unhide_0300()
    Dim Rng     As Range
    Dim cell    As Range
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    Cells.EntireRow.Hidden = False
    For Each cell In Rng
        If cell.Value = "S.2" And cell.Offset(, 11).Value = 0 Then cell.EntireRow.Hidden = True
    Next cell
End Sub

 

[tjktm]

I need to first evaluate all of Column A for first 300 rows for "S.2" , then Column L for value / no value of those rows

Excel 2013/2016

	.	.	.	.	.	.	.

<tbody>
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]S.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 9.00[/TD]

[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]S.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 5.00[/TD]

[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]S.4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 6.00[/TD]

[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]S.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]S.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2.11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 2.00[/TD]

[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

I hope this small screenshot helps. I attempted to name/number my own rows and columns.

The code would need to hide everything except for the first two rows. (Actually rows 2 &3) since they are the only two that have "S.3" in the first column and a dollar figure in Column L.

thanks

 

[Fluff]

How about

Sub HideUnhideRws()
   Dim Cl As Range
   
   Cells.EntireRow.Hidden = False
   
   For Each Cl In Range("A2", 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
End Sub

 

[tjktm]

still doesn't work. it starts at the last "S.3" and then hides every row all the way up the page.

Sub Hide_Unhide_0300()

Dim Cl As Range
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False

For Each Cl In Range("A2", 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

 

[Fluff]

This is what I have to start with

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	S.3											$ 9.00
3	S.3											$ 5.00
4	S.4											$ 6.00
5	S.3
6	S.3
7	2.11											$ 2.00
8	S.3											$11.00
Results

And this is what I end up with

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	S.3											$ 9.00
3	S.3											$ 5.00
8	S.3											$11.00
Results

 

[tjktm]

yes, that is exactly what i need it to do. but isn't working that way on my end

 

[Fluff]

Check that your S.3 values don't have any leading/trailing spaces.

 

[tjktm]

I checked for spaces but still getting the same result. it hides everything from row 1 to the last row containing S.3. Even many other rows above it that contain S.2, S.1 or blank. Any suggestions, i'm stumped.

Thank you very much for your help

 

[Fluff]

Do you have any merged cells?
If not would you be willing to share your file?
To do that you'll need to upload to OneDrive, DropBox or similar, mark for sharing & post a link to the thread.