Hide all rows where one specific cell in that row = 0?
 

Just trying to help my mum complete a stock take for her company. She's
using Excel to calculate the value of the stock with columns for the
code, the name, the quantity, rate and then total (rate * quantity).
Some of the stock isn't always "in stock" but they still stock it on
occasions so it can't be deleted. She wants to be able to hide any
columns that have a total value of 0 quickly. What's the best way to go
about doing something like this? Macro maybe? I don't want to go all the
way through the 2000 somthing items and hide any I see. Any ideas if
that makes sense?

Thanks
Jay


--
jaydevil
------------------------------------------------------------------------
jaydevil's Profile: http://www.excelforum.com/member.php...o&userid=26672
View this thread: http://www.excelforum.com/showthread...hreadid=399444

Jay

I use the follwong code to achieve what you want:

Sub CleanSheetHideRows()
Dim nCol As Integer
Dim J As Integer

Application.ScreenUpdating = False
With ActiveSheet
.Range("B4").Select
nCol = 2
For J = 4 To 6000
If .Cells(J, nCol).Value = 0 Then
.Cells(J, nCol).Select
Selection.EntireRow.Hidden = True
End If
Next J
End With
Application.ScreenUpdating = True
End Sub

B4 specifies where to start from.
nCol is the column number. So 2 is Column B

This code will hide ROWS that equal 0.

If you want to hide COLUMNS do the following:

Sub CleanSheetHideRows()
Dim nRow As Integer
Dim J As Integer

Application.ScreenUpdating = False
With ActiveSheet
.Range("B4").Select
nRow = 2
For J = 2 To 6000
If .Cells(J, nRow).Value = 0 Then
.Cells(J, nRow).Select
Selection.EntireRow.Hidden = True
End If
Next J
End With
Application.ScreenUpdating = True
End Sub

I am sure that more competent person will be able to simplify this code
or help more accuratly! :)


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=399444

Does this just get added to the visual basic editor as is or do I have
to edit it in anyway? I've just tried adding it and it seemed to lock
excel, is that because of the amount of rows or is it because it would
be constantly running if I don't set it to a macro? Sorry if I seem to
be asking lots of questions, I haven't used excel for a long time.

Thanks again


--
jaydevil
------------------------------------------------------------------------
jaydevil's Profile: http://www.excelforum.com/member.php...o&userid=26672
View this thread: http://www.excelforum.com/showthread...hreadid=399444