Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find highlighted cells
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 -- Gary''s Student - gsnu201001 "Dawn" wrote: 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find highlighted cells
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! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu201001 "Dawn" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find highlighted cells
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 -- Cheers, Ryan "Dawn" wrote: 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! "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu201001 "Dawn" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find/Filter highlighted cells | Excel Programming | |||
Find Highlighted Cells | Excel Discussion (Misc queries) | |||
Macro to copy highlighted cells to csv file | Excel Programming | |||
Macro to round highlighted cells to nearest hundred | Excel Programming | |||
Is there a macro which adds selected/highlighted cells? | Excel Worksheet Functions |