Can you conditionally hide a cell?

G

Guest

Guest
I want to use an IF command to hide a cell if it has no value as a way of consolidating my list. I've tried conditional formating but can't find a "hide" selection. Can it be written in code? Any suggestions would be appreciated.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You can't "Hide" a cell with conditional formatting, but you could change the font color to your sheet color if you don't want to see it.
 
Upvote 0
Yes, almost everything is possible...
if you use vba :wink:

Use this code and if the entire row is empty, it will be hidden.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target = "" Then
    Rows(Target.Row).Select
    If Application.CountA(Selection) = 0 Then
    Selection.EntireRow.Hidden = True
    End If
End If

End Sub
 
Upvote 0
Thank you Bruno, but the rows that I want hidden have "0" values in them. Your code is not working to hide them. I still get something like this:

Market Analysis
0
Advertising
0
0
Sales
Etc.

If you can help me with this I would appreciate it very much.
 
Upvote 0
Perhaps my probelem with your code lies in the covnersion. I keep getting an error with this line:

If Target = "A1:A125" Then

I'm pretty sure it's because of the target values but not sure how to correct. Please advise.
 
Upvote 0
Thank you for your help, but the rows are still present after intering the VBA code.
Any suggestions?
 
Upvote 0
just a thought - have you checked that all the cells in the rows are actually empty?
another thought - not sure if this would affect anything, but do you have any merged rows or columns in the target range?
This message was edited by anno on 2002-02-20 19:27
 
Upvote 0
There was something wrong with the previous posting...
(Juan, you may delete it ?)

On 2002-02-20 11:25, sho-nuff wrote:
Thank you Bruno, but the rows that I want hidden have "0" values in them. Your code is not working to hide them. I still get something like this:

Market Analysis
0
Advertising
0
0
Sales
Etc.

If you can help me with this I would appreciate it very much.

Try something like this if you want to hide the cells with "0" and the empty cells and if you only want to check the changed cell :

This code doesn't check other columns.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target = "" Then
    Rows(Target.Row).EntireRow.Hidden = True
ElseIf IsNumeric(Target) Then
    If Target.Value = 0 Then
        Rows(Target.Row).EntireRow.Hidden = True
    End If
End If

End Sub
This message was edited by Bruno on 2002-02-21 02:22
 
Upvote 0
now I'm getting a script error with my target line:

If Target = "$A$1:$A$125" Then

Need I broaden the range or narrow it?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top