Hide row based on cell value

[carics]

Hello,


I am looking for a solution for one of my workbooks to make it easier to read and make it smaller, eliminating not needed or blank areas.


For it I would like to know how can VBA code help me to make: IF A1 = "1" then hide row 1, otherwise do nothing. The same for row 2, 3, 4, and so on for the whole book (if it is too much, I can do with a 1000 rows limit).


Thanks in advance.

 

[vijaySharma]

Hi Carica,


Below is the code

[pre]

Sub HideRows()
Sheets("Sheet1").Select
Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = 1 Then
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

[/pre]

Please change the sheet name and cell address accordingly.

~VijaySharma

 

[carics]

thanks!! Testing........

 

[carics]

ok, tested... it is not exactly what I am looking for.

Lets imagine that A9, A10, A16, A19, A200, A201.... all are marked with "1" so rows 9, 10, 16, 19, 200, 201 should be hidden. I mean, it may not be consecutive rows and it may not start in A1.


What should the code be in this case?


again, thanks for answering

 

[carics]

or I could try to set up all the row numbers that I want hidden in one cell, if it helps...


Do you have any more ideas?

 

[vijaySharma]

This will work for the entire column A if there are no blank cells in between. So all the cells you have suggested above will be hidden when you run the code.


You are free to change the start Column range on the code above... this code works on single column and multiple rows...


~VijaySharma

 

[NARAYANK991]

Hi ,


Change the macro slightly as shown below :


Sub HideRows()

Sheets("Sheet1").Select 'Change as required

Range("A1:A300").Select 'Change as required

For Each cell In Selection

If cell = 1 Then

Range(cell.Address).EntireRow.Hidden = True

End If

Next

End Sub


Change the start address of A1 and the end address of A300 to whatever you want.


Narayan

 

[carics]

wow! that's perfect! only better would be for it to automatically run everytime you change the values in column A so there is an auto hide/unhide action, but taking into account the time it is taking me to do 500rows, I prefer not to wait 20secs every time I change a 0 to a 1.


Thank you a lot! I will keep this one!

 

[dustinshepard]

Hi I need something similar to this, maybe someone can help me adjust?

I need to be able to hide 1 to 42 rows depending on another sheets cell value.


I want to hide row 23 in sheet1 if cell Sheet2!A1 is blank

I want to hide row 24 in sheet1 if cell Sheet2!A2 is blank

I want to hide row 25 in sheet1 if cell Sheet2!A3 is blank

and so on...

 

[bobhc]

dustinshepard

Your post may be similar but it is still unique and you should start your own post and not hide your post in others, those reading the board may assume that there are no more answers for this post and you will not get the help you would with a clearly titled new post of your own