Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specific cell values | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Insert rows | Excel Worksheet Functions | |||
how to hide rows in a protected sheet | Excel Worksheet Functions | |||
AUTO HIDE ROWS | Excel Worksheet Functions |