Excel help

[BrieAndChilli]

Hi

I have a spreadsheet and I want to hide any row that has a 0 in column B. I can do this with the filter but the data come from other sheets so the 0s could change to other data. When this happens I want the row to automatically reappear without having to re-do the filter.

~I am struggling to get it to reappear once it changes from a 0.

Any ideas? I have not idea about macros and what not so stuff I found online doesnt really help me as I dont know what I am doing with VBA or whatever its called!!

[MyUsernameIsUnavailable]

Hey, did you get an answer for this? I can try to help 😊

How does the data populate the rows you are trying to filter and do you have a sense of how regularly your data changes?

[weathervane1]

OP, @MyUsernameIsUnavailable is spot on with the questions asked. By understanding how the data enters your spreadsheet, it will be possible to come up with a solution that is permanent and that will not get overridden each time new data enters. Off the tip of my head, I'd create a small macro (learn to use the macro record function then it will copy what you type in and repeat it when asked) to re-apply the filter each time, and probably attach it to a button you can press. Otherwise I'd write a small vba routine to do similar, again at the press of a button or automatically upon new data arriving. I'm sure that @MyUsernameIsUnavailable will be able to help you with either method but if you do struggle for a solution, PM me in a few days and I'll help.

[weathervane1]

Hi - Apologies up front, I have tried to construct a step-by-step answer but I'm aware that it might come across as more complicated than I intended. Plain text isn't the best medium and the ability to send you a proper Excel file would be easier. Anyway, if you follow what follows one step at a time, it should work (again apologies if it seems badly worded by me). Go into your Excel workbook -->

1. Check the tabs at the top of Excel – do you have one called “Developer” alongside others such as Home, Insert, Review, View and Help?
   If so, go to step 3; if not go to step 2
   
2. Click on the File TAB at the top left and choose options  Customise Ribbon  and in the right column that appears, pop a tick in the Developer check box, and press OK. You should now have a Developer TAB at the top next to Review and Help
   
3. Make sure that you have two sheets (at least) – one for the data you want to work on and one for a couple of buttons we’re going to add.
   I’ve called my sheets “Control” (for the buttons) and “Data Sheet” – if you’re not sure how to rename sheets, you click on the sheet name tabs at the bottom of the screen, right click your mouse, select rename and then rename them.
   If you don’t like the sheet names I’m using, then choose your own but, make sure that you amend the small bit of code that follows in step 5 wherever you see the following (i.e. change the names in quotes wherever they appear in the code to match your sheet names):
   
   
   Worksheets("Control").Select
   and
   Worksheets("Data Sheet").Select
   
   
4. Click on the Developer option at the top and then on the Visual Basic option that appears underneath on the left
   
5. A set of small windows will appear. If you right click in the left window on the words “Sheet1(Data Sheet)”, and select View Code, you will now be able to type into a largish window on the right.
   Copy and Paste in the following code that appears between (but not including) the s below (the bits that will appear in green are purely for comments to explain what each line does) – note it looks messy below but will look different when you paste it across:
   
   Sub HideRowsWithZeroInColumnB()
   
   Worksheets("Data Sheet").Select
   'Moves control to the sheet with the data in
   
   Dim LastRow, RowCount As Long
   'Sets up a few variables to remember things as the program runs
   
   LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
   'Gets the last row in the table
   
   Call Un_HideRowsWithZeroInColumnB
   'Calls the code in the block below to reset the data to restore any previously hidden rows
   
   For RowCount = 1 To LastRow
   'Starts at the first row of data
   
   If Cells(RowCount, 2) = 0 Then
   'Looks at each row and sees if there is a zero in column B
   
   Rows(RowCount).Hidden = True
   'Hides each row that has a zero in column B
   
   End If
   
   Next
   'Moves onto the next row
   
   Worksheets("Control").Select
   'Moves control back to the sheet with the two buttons on it
   End Sub
   Sub Un_HideRowsWithZeroInColumnB()
   
   Worksheets("Data Sheet").Select
   'Moves control to the sheet with the data in
   
   Rows.EntireRow.Hidden = False
   ''Resets the data to restore any previously hidden rows
   
   Worksheets("Control").Select
   'Moves control back to the sheet with the two buttons on it
   End Sub
   *
   
6. Then click the red cross in the top right of the code window and return to “normal” Excel
   
7. Click on the Developer option at the top again and then Insert (it’s a picture of briefcase) under Developer
   
8. Under Form Controls, choose the top left image of a button and using the mouse, draw a button on the screen.
   Repeat for a second button next to the first.
   
9. Right click each button in turn and choose Edit Text. Make the first button read something like “Hide Rows with Zeros in Column B” and the second “Un-Hide all Rows” (a reset option).
   
10. And the final stages: right click the first button and select Assign Macro. In the choices box that appears, click on the name with the phrase “HideRowsWithZeroInColumnB()” in it (it might also have the name of your file in) but either way, choose the one with “HideRowsWithZeroInColumnB()” and press OK
    
11. Right click the second button and select Assign Macro. In the choices box that appears, click on the name with the phrase “UnHideRowsWithZeroInColumnB()” in it (it might also have the name of your file in) but either way, choose the one with “UnHideRowsWithZeroInColumnB()” and press OK.
    Save your file BUT in the File Save As options, choose file type as “Excel Macro-enabled Workbook (.xlsm)” – if you don’t, your new buttons won’t work.
    Now, if you put some data in the data sheet and then go to your control (buttons) sheet, it should hide and unhide as you want it to.
    Apologies if it seems a lot to do and I’m hoping I’ve not missed much – not easy on a phone. PM me if you need any help tomorrow.

[BrieAndChilli]

Thanks, I still have not been able to work this out!!

I have 2 sheets in the same workbook

The first one is made of the the following forumla in column B - so if the result is 0 I want the row to be hidden - The A ceolumn data changes according to options chosen from a drop down in another sheet
=IFERROR(HLOOKUP($A2,Desserts!$D$1:$CD$13,10,FALSE),0)


The second sheet there are 2 columns I want hidden-
If column B is 0 I want the row hidden, THEN if column C is also 0 I want the row hidden.

Column B data is informed by the following foruma: ='Shopping Categories'!A2 with the A2 changing through out the list

Column C data is informed bu the following formula:=SUMIFS(Table8//#All],[Column4,Table8//#All],[Column1,B5)

Any help would be appreciated, I can get thr rows to hide but if any of the input data is changed so the cells are no longer 0 the rows do not unhide!

[tribpot]

I wouldn't use the macro approach that @weathervane1 has provided purely because if you need to send the spreadsheet to anyone else the fact that it's a macro-enabled workbook could cause issues.

It sounds like all you need to do is put a filter on the two columns to filter where the value is greater than zero

It doesn't automatically unhide the row when the value changes, but all you need to do is click on the 'reapply' button in the ribbon.

Pictures attached.

[weathervane1]

@tribpot is quite correct in terms of lots of email systems etc tend to warn about macro-enabled files. The simpler alternative is where I started but I assumed that the OP knew how to do filters and resets and had discounted it as a method. If not then go for it! Much simpler.

[weathervane1]

@tribpot ... and it took ages to type out lol.!

[tribpot]

Yes sorry @weathervane1 I didn't want to trash your work! You've gone into massive detail to help the OP. I avoid macros basically completely, but won't even use Power Query on a spreadsheet that I have to send out - it doesn't trigger alarm bells with the email system but the end user sees a warning about 'external data connections have been disabled', which seems too alarming (not to mention annoying when there aren't any).

So it's formulas all the way for me, not that you can hide rows with a formula!

[BrieAndChilli]

I had already looked at the filter option but did wnat something that would automatically do it as some people that will use the spreadsheet are not very technical!!
It will be stored in a share folder in onedrive for people to use as a template so will that cause any problems if macros are enabled?

When I have time I will do the above coding as if it works then I will use it on my version! once set up it is intended to be used independantly by users so data I add/amend will just affect my version.

[weathervane1]

OP - speak to your IT people. Shared folders in themselves won't cause an issue but there will be a warning when people open it I expect. But if everyone knows it's a safe spreadsheet, then the warning should only appear once. Anti viruses may also flag it. It's to protect you from dodgy macros etc. I use VBA (Visual Basic) for a lot of excel work but it can cause issues with a number of systems. Anyway, if you want to try my solution, it will work for one column of zeros. If you want it amended for your slightly bigger question, PM me.

[BrieAndChilli]

I've just done a test and the buttons work great on the desktop version but I have just uploaded it to onedrive and opened using 365 as that is what a lot of poeple will be using to view it and it says form controls not allowed on online version only desktop. It is for scouts so we only have 365 provided (i have full microsoft on my laptop from work)
The above worked great for my version thanks so I will use it and if others also have desktop versions they can have that copy but I will create another version with some simple instructions of using the filter - they will have to unfilter and then refilter everytime they change data

I think for the 2nd column I will set up some conditional formatting to change the row colour or something so it is obvious what rows have data.

I hate using the online version as so many features are disabled or not able to be fully utilised!

Thanks for all the help and advice!

[weathervane1]

If you want me to adjust the code for two columns I'm happy to do so but can we please do it via PM / email... the thought of reposting code makes me shiver lol. Ps - I've already asked Mumsnet to forward an example spreadsheet today and their response was a big fat No.. and a sarcastic email about how they are moderators and won't help.

[weathervane1]

The irony is that 365!is not cheap over a year and yet for less than £25 you can purchase a copy of MS office professional that will last a lifetime.

[weathervane1]

OP, there may be a way around the form controls issue. I'll look into it tomorrow and let you know.

[weathervane1]

OP, if you look back to step 8 where we use a form control, it seems that ms 365 online doesn't like them - no idea why, I don't use it. One thing that might work (and maybe others can advise), is to start at step 8 from the file you've already saved (having already done everything else), and when choosing a button, select the one lower down in the selection box under the heading Active X controls. Then repeat the other steps to attach the two macros to it and rename the two new buttons as before. Once you're satisfied that the new ones work the same, right click the old buttons and delete them and save the file as before. It's only a best guess but I think active X controls shouldn't have the same limitations as the form control buttons. As I don't have MS 365 it's at best a decent guess. Worth trying I think, especially if it allows other users to benefit. Oh... and save the original file first and make a copy for this test. Good luck!