Do While or For Loops?

[Kaladin]

I have a table that looks kind of like this:

T/F	True	False	True	True	False	False	False	True
A
B
C
D
E
F
G
H

<tbody>
</tbody>

The values in the blank spaces are either "N/A" or a number. I'm attempting to write something that will hide the row if all of the values under the columns marked "True" are "N/A" but will unhide the row if at least one of the values under a column marked "True" is a number.

I started out with a for loop going across the top to check for "True"s before going down the rows but it would just treat each column individually, instead of checking all "True" columns. Any tips about what I should be doing different are more than welcome.

If you're curious, this is for a dynamic chart to hide the irrelevant categories in an axis if they have no data.

 

[JoeMo]

You indicate you want to hide or unhide rows, but your description suggests you mean columns not rows. Please clarify. What cell is the first true/false header in?

 

[Kaladin]

The first true/false header is C1. I do want to hide or unhide rows. The row is to be hidden if the cells under the "True" columns only have "N/A" in them. I'm sorry if I'm not explaining this well. English is hard.

 

[JoeMo]

The first true/false header is C1. I do want to hide or unhide rows. The row is to be hidden if the cells under the "True" columns only have "N/A" in them. I'm sorry if I'm not explaining this well. English is hard.

What's in columns A & B? We need to know more about the layout of your data to help you. Can you post a sample of your data?

 

[Kaladin]

The column A is empty. Column B has a list of company names. Sorry about the spacing, I don't know how to use the reply thing very well.

Checkbox	TRUE	False	True	True	False	False	False	True	False
Company	Twitter Followers	INS Twitter Followers	Facebook Likes	LinkedIn Followers	INS LinkedIn Followers	Google+ Followers	INS Google+ Followers	Youtube Subs	INS Youtube Subs
A	80227	N/A	#	#	#	#	N/A	#	N/A
B	198551	#	#	#	N/A	#	N/A	#	N/A
C	150925	#	#	#	N/A	#	N/A	#	N/A
D	346957	#	#	#	#	#	#	#	#
E	175202	0	#	#	N/A	#	N/A	#	N/A
F	334049	#	#	#	N/A	#	N/A	#	N/A
G	353325	#	#	#	#	#	N/A	#	N/A

<tbody>
</tbody>

 

[JoeMo]

The column A is empty. Column B has a list of company names. Sorry about the spacing, I don't know how to use the reply thing very well.

Checkbox	TRUE	False	True	True	False	False	False	True	False
Company	Twitter Followers	INS Twitter Followers	Facebook Likes	LinkedIn Followers	INS LinkedIn Followers	Google+ Followers	INS Google+ Followers	Youtube Subs	INS Youtube Subs
A	80227	N/A	#	#	#	#	N/A	#	N/A
B	198551	#	#	#	N/A	#	N/A	#	N/A
C	150925	#	#	#	N/A	#	N/A	#	N/A
D	346957	#	#	#	#	#	#	#	#
E	175202	0	#	#	N/A	#	N/A	#	N/A
F	334049	#	#	#	N/A	#	N/A	#	N/A
G	353325	#	#	#	#	#	N/A	#	N/A

<tbody>
</tbody>

The True/false values are being returned to the first row by checkboxes? If any row below row 2 has an N/A in it you want to hide the row - is that correct?

 

[Kaladin]

Yes, the true/false values are being returned by checkboxes.

I only want to hide a row if ALL the cells in that row with a corresponding TRUE in row 1 show "N/A".

So if row 50 has "N/A" under 3 of the 4 "TRUE" columns then it's not hidden because it only has "N/A" under 3 of the TRUE columns instead of 4. Is this making any sense?

 

[JoeMo]

Yes, the true/false values are being returned by checkboxes.

I only want to hide a row if ALL the cells in that row with a corresponding TRUE in row 1 show "N/A".

So if row 50 has "N/A" under 3 of the 4 "TRUE" columns then it's not hidden because it only has "N/A" under 3 of the TRUE columns instead of 4. Is this making any sense?

Yes, it's making sense. I have to go offline now. If no one else has given you a solution when I return, I will try to provide one for you.

 

[Kaladin]

Awesome. I really appreciate it. I'm just stuck right now.

 

[JoeMo]

The code below assumes the layout shown here. If I understand what you want correctly, only row 3 of the data below will be hidden after the code executes. Note that I'm using "N/A" as you posted for the criteria to hide. If you have formulas in your cells that are returning "#N/A" (w/o the quotes) you will have to change N/A to #N/A.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	*	TRUE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE
2	Company	Twitter Followers	INS Twitter Followers	Facebook Likes	LinkedIn Followers	INS LinkedIn Followers	Google+ Followers	INS Google+ Followers	Youtube Subs	INS Youtube Subs
3	A	N/A	65	N/A	N/A	97	97	97	N/A	N/A
4	B	198551	100	100	100	N/A	100	N/A	100	N/A
5	C	150925	100	100	100	N/A	100	N/A	100	N/A
6	D	346957	100	100	100	100	100	100	100	100
7	E	175202	0	100	100	N/A	100	N/A	100	N/A
8	F	334049	100	100	100	N/A	100	N/A	100	N/A
9	G	353325	100	100	100	100	100	N/A	100	N/A
Sheet5

Sub HideRowIfAllTrueNA()
Dim R As Range, c As Range, i As Long, Cols As Long, ct As Long
Set R = Range("B2").CurrentRegion
Cols = Application.CountIf(R.Rows(1), "True")
Application.ScreenUpdating = False
R.EntireRow.Hidden = False
For i = 3 To R.Rows.Count
    For Each c In R.Rows(i).Cells
        If c.Offset(-i + 1, 0) = "True" Then
            If c.Value = "N/A" Then
                ct = ct + 1
            Else
                Exit For
            End If
            If ct = Cols Then
                R.Rows(i).Hidden = True
                ct = 0
            End If
        End If
    Next c
Next i
Application.ScreenUpdating = True
End Sub