Macro to find highlighted cells

D

Dawn

Hoping someone can help me with a macro. I need to look at each row (except
for header row 1) ... if the cells in the row say "true" that row can be
hidden. If any cells in the row say "false" that row should stay visible.
Then for the remaining visible cells, look at each column (except for column
A), and if everything in that column is true, it should hide that column. If
the column has a "false" in it, that column should stay visible. If it
makes a difference, I'm searching for the true/false *values* that are
returned from a formula that is comparing two individual cells. (I'm
comparing two massive spreadsheets to make sure the data matches up from a
data upload into a database. I want to be able to zero in on the places
where we have mismatches that need review.)

I am capable of recording macros, and copying/pasting to cobble code
together in a VBA window but can not write code. I can't get anywhere close
with a recorded macro on this. Any ideas would be much appreciated, thanks!
 
G

Gary''s Student

Look at:

Sub SearchForFalsehood()
Columns("B:IV").EntireColumn.Hidden = True
Rows("2:65536").EntireRow.Hidden = True
For Each r In ActiveSheet.UsedRange
If r.Value = False Then
r.EntireColumn.Hidden = False
r.EntireRow.Hidden = False
End If
Next
Cells(1, 1).EntireRow.Hidden = False
Cells(1, 1).EntireColumn.Hidden = False
End Sub
 
D

Dawn

Huzzah, works beautifully! I did have a few columns in my sheet that were
blank (neither true nor false) which at first meant it was unhiding the
all-true rows as well. But once I got rid of the blank columns it worked
great (just adding that for the benefit of anyone else who happens to see
this string)

Thanks so much!
 
R

Ryan H

You could use this which would adjust to any number of rows or columns. Hope
this helps! If so, let me know, click "YES" below.

Sub HideRows()

Dim LastRow As Long
Dim LastColumn As Long
Dim cell As Range
Dim col As Long

' unhide all rows first
Cells.Rows.Hidden = False

' find last cell in Col.A
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

' hide rows if cell value is TRUE
For Each cell In Range("A2:A" & LastRow)
Rows(cell.Row).Hidden = Not cell.Value
Next cell

' find last cell in Row 1
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

' hide columns if all cells are TRUE
For col = 2 To LastColumn
Columns(col).Hidden = True
For Each cell In Range(Cells(2, col), Cells(LastRow, col))
If cell.Value = False Then
Columns(col).Hidden = False
Exit For
End If
Next cell
Next col

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top