Excel Hiding Rows with IF statement - VBA?

Question

Can I hide rows in excel depending upon the outcome of another cell that contains an IF statement?

For example, In a certain cell I have an IF statement that answers "Yes" or "No" -- I would like to hide rows if the result of that IF statement is "No." and I would like to show rows if the result of that IF statement is "Yes."

I had a few people tell me to try VBA but I've been watching tutorials and really can't find a good answer to this.

Thanks!

Answer 1

You don't state which cell contains your IF statement, so the following example uses A1 as the cell containing the function. Also, you don't specify the rows you wish to 'toggle' as visible/hidden. This example uses rows 2:5, and 9.



If your cell is not A1 and your rows not 2:5, and 9, modify the code before copying:



Change the two "A1" references to your cell reference, i.e. "C12", "M25", etc.



Change the two "2:5" references to your actual rows to hide. You can use the following types of range references:



One contiguous range: Range("A5:A20")

Individual cells: Range("A1,A4,A5,A12")

A non-contiguous Range: Range("A2:A9,A22,A26,A40:A55")



Modify to suit your needs.



Then, copy the event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Range("A1").Value) = "YES" Then

Range("A2:A5,A9").EntireRow.Hidden = False

ElseIf UCase(Range("A1").Value) = "NO" Then

Range("A2:A5,A9").EntireRow.Hidden = True

End If

End Sub



Select the worksheet containing the IF statement and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button w/white 'x' - top right).



As the IF function returns the Yes/No values, the rows specified will be alternately hidden/visible.

Answer 2

1

Answer 3

Vba If Statement

Answer 4

If Statement Vba

Answer 5

My first instinct would be to place this information into a database (or link it). Then retrieving the information you desire would be trivial. The only efficient means of retrieving multiple rows of data from excel I can think of would be using an autofilter to filter the data in place, or creating a macro with an advanced filter to retrieve the rows and show them on another worksheet. Otherwise you'd use a macro to check the value for every row and retrieve the wanted rows into another worksheet, which might be feasible for 2000 rows, but much slower to 20000.

Answer 6

Yes you can hide rows for example



'create the variable

Dim bRow as Boolean

'set the variable

bRow = false



If bRow == true then

'Sets row 5 to hidden

ActiveSheet.Rows("5:5").Hidden=True

end if



'or you could hide all rows like this

Selection.EntireRow.Hidden = True





'Using object to decide like you suggested



To assign an Object to an Object type variable you must use the key word "Set". eg:



Sub SetObJect()



Dim rMyCell as Range



Set rMyCell = Range("A1")



End Sub