Forum Discussion
Excel / Auto Hide Rows
FOR EX.
Below represents 4 rows and 2 columns of data that are populated from a different tab inside my spreadsheet.
I want to create a formula that automatically hides the entire row labeled E2 b/c there is not a number in the second column.
Is there an easy formula for this?
| TAKEOFF 1 | 3,554 | Tons |
| ~Piers | 3,553.5 | Tons |
| E1 | 3,553.53 | Tons |
| E2 | - | Tons |
There is nothing in what you just posted that suggests you even read the prior response. Did you? Did you try either of the ideas presented there? So far as I know there is no formula, per se, that would hide the row although--as suggested in my prior response--it depends on what you mean by "hide."
The FILTER function would do it, on a separate page. And there was even a link to a page with instructions on how to use it.
Otherwise you're looking at a macro or a VBA routine, and that's not my field of expertise.
Hello! Sorry to awaken an older discussion. But I am facing the same issue and tried the FILTER function and absolutely love the functionality. However, I am trying to fill a cell that is merged and causing a Spill error. Due to the cells above I need to be able to maintain this merged cell. Can you think of any tricks to help me with this? I feel like I am so close to a solution with the FILTER function! Thank you! mathetes
I'm sorry to have taken so long to get back. We've been on vacation in another country and I went, quite intentionally, without a computer. Only a phone, and that only for email and texts and calls, and those on a limited bases.
So with that, if you still are patiently waiting, would it be possible for you to post a copy of your workbook or a reasonable facsimile (in order to avoid disclosing private or confidential info), on OneDrive or GoogleDrive? Then paste a link here that grants access.
Taylor_Watson To expand on mathetes response. you say, "are populated from a different tab". How is that being done? cell-based formula? pivot table? Because if it is the former then mathetes suggestion is perfect because you can just add a conditional to that formula to ignore that data if the corresponding data in column X is blank. You should be able to achieve similar results using a pivot table but use the filter option instead of an in-cell formula. Maybe if you supplied a sample sheet we could aid better.
But as mathetes said, there is no in-cell formula to hide a row. In-cell formula ONLY determine what to display in the cell in which it is located and has no ability to act on anything else.
That all said, there are 2 other options: VBA (also called macros), or Conditional Formatting
Using VBA you can create code to hide that row, but would need to define the 'trigger' action (i.e. when should the code run and perform a check. )
Using conditional formatting you can check if that cell is blank and format that whole row to have the same text and background color. This will 'hide' the information in that row but not truly hide the row as the row will still be present, but it will look blank.
