VBA - clear contents of a particular range if cell is blank

[ExcelMike21]

Hi

Found some really useful tips on the board but this is my first post as I cannot find the answer after searching.

As above, I am trying to write a vba that will do the following:

If any cells in range C8:C32 are blank, then I would like to automatically clear the contents in the adjacent cells in columns D:T.

For example, if C8 is blank, I need to automatically clear contents in cells D8:T8, however, it then needs to look at C9, C10 all the way through to C32 (regardless of what the previous event was for the row above).

For context, range C8:C32 (named "Dates"), contains dates that are automatically populated each month, however, they move up or down depending on the day of the month. i.e. Wednesday 1st April would start in C10 (C8 being Monday, C9 being Tuesday). Please also note, when I say C8:C32 are blank, I mean they contain a formula that is currently showing the cell to be blank, not fully empty.

I have played around with various things I've seen on the forum and the closest I can get is as follows:

But, I'm struggling to get the above to work for the full range of cells and it's as if I would need to type that out for every cell/row I want it to apply to (and I know there must be a simpler way!)

Really appreciate any help or advice.

Please let me know if you need more information or if anything doesn't make sense!

Thanks,
Michael

 

[ExcelMike21]

Sorry, not sure if the code worked there - this is what seems to come close so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("C8") = "" Then
Range("D8").ClearContents

End If
End Sub

 

[MARK858]

Just want to be 100% sure before typing that you want the code to run from a Selection_Change (or other worksheet event) and not from a button as the code is different.

 

[ExcelMike21]

Just want to be 100% sure before typing that you want the code to run from a Selection_Change (or other worksheet event) and not from a button as the code is different.

Yes a selection or worksheet change event please, not a button. Thanks!

 

[MARK858]

Maybe (as a Worksheet_Change)...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        For Each myCell In Range("C8:C32")
            If myCell.Value = "" Then Intersect(myCell.EntireRow, Columns("D:T")).ClearContents
        Next
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Or as I can read the question 2 ways...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
 
            If Target.Value = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

 

[ExcelMike21]

Maybe (as a Worksheet_Change)...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        For Each myCell In Range("C8:C32")
            If myCell.Value = "" Then Intersect(myCell.EntireRow, Columns("D:T")).ClearContents
        Next
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Or as I can read the question 2 ways...

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Target.Value = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
   
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Maybe (as a Worksheet_Change)...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        For Each myCell In Range("C8:C32")
            If myCell.Value = "" Then Intersect(myCell.EntireRow, Columns("D:T")).ClearContents
        Next
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Or as I can read the question 2 ways...

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Range("C8:C32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Target.Value = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
   
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Thank you for this MARK858, really appreciate the quick response too! I've tried both codes and they work, however, before any content is cleared from D:T, I am required to click into the cell in column C and press enter. Is there a way to automate this?

It seems to do exactly what I want by using the following extract from your code and running it as a Worksheet_SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range

        For Each myCell In Range("C8:C32")
        If myCell.Value = "" Then Intersect(myCell.EntireRow, Columns("D:T")).ClearContents
        Next
End Sub

But, I was worried I would run into problems by removing the 'With Application False/True' parts.

Furthermore, I am also running the following Worksheet_Change event to auto-fill columns R & S based on a data validation/dropdown list that the user selects in column Q. It is filling from data held in columns X and Y:

Private Sub Worksheet_Change(ByVal Target As Range)

'run the macro when Leave Reason is selected from the dropdown menu of range Q8:Q32
    If Not Intersect(Target, Range("C8:C32")) Is Nothing Then

    If Target.Offset(0, 9) > 0 Then
    Target.Offset(0, 1) = Target.Offset(0, 7)
    Target.Offset(0, 2) = Target.Offset(0, 8)
    
    End If
    End If
End Sub

But, when both codes for these separate events are added in the same worksheet, an error code 13 "mismatch" is given and the "If Target.Offset(0,9)>0 Then" line is highlighted in the debugger. Both codes work as intended when the other is not present).

I'll try and summarise exactly what I want to happen to see if there is a better way to achieve this.

Range C8:C32 contains date values. Depending on the day in which a month starts or ends, these dates can move up or down, leaving blanks where not applicable. For example, April 1st starts on Wednesday so cells C8 (Monday) and C9 (Tuesday) would be blank and C10 (Wednesday) would contain April 1st. This will change from month to month and the column will be locked from user editing/selection.

Range D8 to T32 requires users to enter data, column Q contains a data validation list, which, if selected, runs a VBA to automatically fill column R and S with the values held in X and Y, respectively. (X and Y are being calculated by a Vlookup to another sheet which contains variables). I couldn't use an IF formula in column R & S because users will still need the ability to manually alter this value from time to time.

In short, if any cell in the Range C8:C32 blank, I want that to override and automatically block/clear entry from the corresponding columns D:T.

So, if C8 is blank and they attempt to type anywhere from D8:T8, including selection of the data validation in Q, I want that to immediately clear when you press enter or move to another cell.

I'm not sure if there is a simpler way to incorporate all of the above together?

I hope that makes sense!

Thanks,
Michael

 

[MARK858]

So, if C8 is blank and they attempt to type anywhere from D8:T8, including selection of the data validation in Q, I want that to immediately clear when you press enter or move to another cell.

Then your event should be running when a manual change is made in columns D:T not C then testing if C is "" and clearing contents, yes?
Are they manually changing one cell and not copying/pasting multiple cells in the range?
I take it one of your options in the dropdown is a blank?

As for the Application parts.
.ScreenUpdating just stops the screen updating. thus stops flicker and speeds up code and so can be removed safely.
.EnableEvents stops the code triggering other events when it runs or even (in some cases) retriggering itself causing an endless loop.

 

[ExcelMike21]

Then your event should be running when a manual change is made in columns D:T not C then testing if C is "" and clearing contents, yes?
Are they manually changing one cell and not copying/pasting multiple cells in the range?
I take it one of your options in the dropdown is a blank?

Yes, that would be correct to test if C is "" when a change occurs in D:T, apologies if I did not explain that well earlier.
They would be manually changing each cell from D:T, never copy and pasting multiple cells.
Yes, one of the dropdown options in Q is blank.

As for the Application parts.
.ScreenUpdating just stops the screen updating. thus stops flicker and speeds up code and so can be removed safely.
.EnableEvents stops the code triggering other events when it runs or even (in some cases) retriggering itself causing an endless loop.

Thanks for the info regarding Application parts. Really useful to know as I'm just starting out in VBA (as you can probably tell!)

Thanks,
Michael

 

[MARK858]

Try the codes below (both in the worksheet module) and your sub being called by the dropdown should be a regular sub in a regular module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    If Not Intersect(Target, Range("D8:T32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Cells(Target.Row, "C") = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
            'Replace ? with the dropdown result that calls the macro in your data validation
            If Cells(Target.Row, "Q") = "?" Then Call YourOtherRegularCodeName
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D8:T32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Cells(Target.Row, "C") = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
            'Replace ? with the dropdown result that calls the macro in your data validation
            If Cells(Target.Row, "Q") = "?" Then Call YourOtherRegularCodeName

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

 

[ExcelMike21]

Try the codes below (both in the worksheet module) and your sub being called by the dropdown should be a regular sub in a regular module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Not Intersect(Target, Range("D8:T32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Cells(Target.Row, "C") = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
            'Replace ? with the dropdown result that calls the macro in your data validation
            If Cells(Target.Row, "Q") = "?" Then Call YourOtherRegularCodeName
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D8:T32")) Is Nothing And Target.Cells.Count = 1 Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

            If Cells(Target.Row, "C") = "" Then Intersect(Target.EntireRow, Columns("D:T")).ClearContents
            'Replace ? with the dropdown result that calls the macro in your data validation
            If Cells(Target.Row, "Q") = "?" Then Call YourOtherRegularCodeName

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub

That runs perfectly! Thank you so much for your help today, MARK858 - I really appreciate it!

Thanks,
Michael