Hiding rows based upon a countif condition

[macca_18380]

Hi, Again please forgive my lack of understanding, i'm learning but still a good way to go.

I would to automatically hide rows in a spreadsheet if input cells within a range are blank. In this case if all cells in the range J8 to AC8 are blank i'd like to hide rows 11 to 96, so i was thinking a countif function would be appropriate, but it doesn't work. Any help on correcting this code, or anything else that would work, would be very helpful, thank you!

If Not Intersect(Target, Range("J8:AC8")) Is Nothing Then

If CountIf(Range("J8:AC8"), "") = 20 Then
Rows("11:96").Hidden = True
End If

End If

 

[Joe4]

You can replace all this:

If CountIf(Range("J8:AC8"), "") = 20 Then
Rows("11:96").Hidden = True
End If

with just this one line of code:

If Application.WorksheetFunction.CountBlank(Range("J8:AC8")) = 20 Then Rows("11:96").Hidden = True

(you have to use "Application.WorksheetFunction" to use Excel functions in VBA).

However, I am concerned about this line of code:

If Not Intersect(Target, Range("J8:AC8")) Is Nothing Then

If you are not updating a cell between J8 and AC8, this code will never run!

 

[macca_18380]

You can replace all this:

If CountIf(Range("J8:AC8"), "") = 20 Then
Rows("11:96").Hidden = True
End If

with just this one line of code:

If Application.WorksheetFunction.CountBlank(Range("J8:AC8")) = 20 Then Rows("11:96").Hidden = True

(you have to use "Application.WorksheetFunction" to use Excel functions in VBA).

However, I am concerned about this line of code:

If Not Intersect(Target, Range("J8:AC8")) Is Nothing Then

If you are not updating a cell between J8 and AC8, this code will never run!

Thank you Joe, the code you have supplied works perfectly and has allowed me to improve coding elsewhere also. Thank you for explaining the application.worksheetfunction also, that is very helpful. Thanks again!

 

[Joe4]

You are welcome.
Glad I was able to help!