help with code to automatically hid rows if a cell is empty

[bleeet]

hello

i am not good with VBA but i want to make a code in one of of the pages in my excel file so that if nothing appears in a cell in the range of C4 to C23 then the row is automatically hidden and and if something appears the row unhides

dunno if this matters but those cells will show info that come from info on another sheet.

i saw this code on another thread here but i don't know how to adjust this to fit my setting


Private Sub Worksheet_Calculate()
Dim i As Long
For i = 9 To 53
Rows(i).Hidden = Range("A" & i).Value = ""
Next i
End Sub

if anyone knwo how to do this or advice on a better idea please help

i hope you guys understand what i am saying

 

[bleeet]

Yes, you put the code in the right place.

If the sheet is filtered you will have drop down arrows in row 1.

The code will fail if there are error values like #N/A. Are there errors?

hrmm

i have if statement codes in these cells which are connected to another sheet so if there is no info put on another sheet the rows are hidden

i don't see any #n/a right now

i guess it's not filtered then because i don't see a drop down arrow on row 1

 

[VoG]

This is a more robust version that will handle error values.

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 4 To 23
    If Not IsError(Range("C" & i)) Then
        Rows(i).Hidden = Range("C" & i).Value = ""
    End If
Next i
End Sub

Try creating a new workbook, add the code, in C1 enter

=A1+B1

and see if it works. If it does then there is some peculiarity with the sheet you were testing it on.

 

[bleeet]

This is a more robust version that will handle error values.

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 4 To 23
    If Not IsError(Range("C" & i)) Then
        Rows(i).Hidden = Range("C" & i).Value = ""
    End If
Next i
End Sub

Try creating a new workbook, add the code, in C1 enter

=A1+B1

and see if it works. If it does then there is some peculiarity with the sheet you were testing it on.

yea it worked when i created a new workbook

I have if statements in on C collum that stay something if something is inputed on another page in the workbook i don't know if that is the problem

if there is a way i could send it to you so you could see you would probably have a better understanding

 

[VoG]

You can upload it to a site like www.box.net and provide a link in a reply to this thread.

 

[bleeet]

i hope this works

http://www.box.net/shared/zdsxys3arg

 

[bleeet]

i want to put the code in the GLs 2 page

 

[VoG]

Try this

Private Sub Worksheet_Calculate()
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For i = 4 To 23
    If Not IsError(Range("C" & i)) Then
        Rows(i).Hidden = Range("C" & i).Value = ""
    End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

 

[bleeet]

works!! so far so good

thank you so much for your help

i don't knwo what you did but it seems to work now

thanks

 

[bleeet]

sorry another question

how do i protect this sheet and still allow the rows to move and a person to be able to highlight and copy cells?

and sometimes it works then it doesn't after i try to protect it then close the work (not saving) then reopen

 

[VoG]

Maybe

Private Sub Worksheet_Calculate()
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Me.Unprotect password:="password"
For i = 4 To 23
    If Not IsError(Range("C" & i)) Then
        Rows(i).Hidden = Range("C" & i).Value = ""
    End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Me.Protect password:="password"
End Sub