Hide two columns if one is blank

[ILoveCheese]

Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?

I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.

Sub HideColumnsInd()
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
    If rCell = "" Then
        rCell.EntireColumn.Hidden = True
    Else
        rCell.EntireColumn.Hidden = False
    End If
Next rCell
Application.EnableEvents = True
End Sub

Any help would be appreciated.

Thank you.

 

[JimM]

Am I missing something here or doesn't your code in effect just look at cell D48 and hide the entire column if it's empty ??

 

[ILoveCheese]

You are correct. I'll have to work on that. Any ideas on my original question?

 

[schielrn]

So if any cell in D3:D48 is blank do you want column C and D hidden? Or if all the cells are blank? You cannot hide just a single cell if that is what you are after? You can either hide a whole row or a whole column?

 

[ILoveCheese]

If D is blank then I would like both C and D (entire columns) to be hidden.

 

[mooseman]

This is just for the range you had in your original post;
I had this sitting around, modify as needed.

Sub HideEmptycolumn()

    Application.ScreenUpdating = False

    Range("D3:D48").Select
    
    For Each c In Selection.Cells
      If c.value <> "" Then
      Exit Sub
        Else
        If c.Row = 48 Then
            Columns("C:D").Hidden = True
            End If
            
       End If
    Next c

    Application.ScreenUpdating = True

End Sub

 

[schielrn]

But is that any cell in that range D, because as the previous person posted, the code is really only dependant upon D48, there is no need to loop through any other cells?

 

[mooseman]

But is that any cell in that range D, because as the previous person posted, the code is really only dependant upon D48, there is no need to loop through any other cells?

He said that his original code was incorrect. I just went with what was posted. It's not finished, but it is a starting place for ILC....

 

[ILoveCheese]

Moose your code works perfect. Thanks a bunch! And schielrn, thanks as well. I appreciate your time.

Thx again!

 

[jasonb75]

Assuming that you only want the columns hidden if all cells in that range are blank, you only need one line of code to hide them.

If Application.WorksheetFunction.CountA(Range("B3:D48")) = 0 Then Columns("C:D").EntireColumn.Hidden = True

Or to hide if 1 or more are blank

If Application.WorksheetFunction.CountA(Range("B3:D48")) < 46 Then Columns("C:D").EntireColumn.Hidden = True

edit:- might not work if the cells contain a formula that returns a blank (untested)