VBA Code to clear cells when specific cell is empty

[Juddman79]

Evening all,

I'm hoping that somebody might be able to help with some VBA code to clear cells if a specific (date) cell is blank. I have 2 worksheets that are set up differently but I need the same thing to happen in both sheets.

Sheet 1 (VBA code 1)

E7 - E24 are cells filled with dates from another sheet. I need a some coding that clears the cells in alternative columns (G7, I7, K7, M7 etc until cell BE7) along the row if the E cell is blank. Cells H7, J7, L7 etc are formulas that calculate expected due dates based on the date in cell E7 so will be protected once the sheet is complete. There will not be a password protecting the sheet.


Sheet 2 (VBA Code 2) (Image below)

If Column F has no date in the cell then cells in the completed Date and Result Rows are cleared. so if there is no date in F3 then the cells from J4 and J5 through to DE4 and DE5 are cleared.

The same would need to apply for the dates entered in Column F down to F54 to clear J64 and J65 through to DE64 and DE65.

Hope this makes sense.

I've looked at older threads but haven't found anything that works so any help is really appreciated. Many thanks

 

[dreid1011]

When should the code trigger?

 

[Juddman79]

When should the code trigger?

So on the Sheet 'index' the cell B5 (Date) information populates cell E7 (sheet 1) and F3 (sheet 2)

Cell B7 (Date) then populates E8 (sheet 1) and F6 (sheet 2) and so on for 19 rooms.

I would the code to trigger when the date information on the index sheet is cleared which then clears the date cells on sheets 1 and 2.

Hope I've explained myself well enough. Thanks for your help.

 

[dreid1011]

So on the Sheet 'index' the cell B5 (Date) information populates cell E7 (sheet 1) and F3 (sheet 2)

Cell B7 (Date) then populates E8 (sheet 1) and F6 (sheet 2) and so on for 19 rooms.

I would the code to trigger when the date information on the index sheet is cleared which then clears the date cells on sheets 1 and 2.

Hope I've explained myself well enough. Thanks for your help.

Mostly, yes, but a couple of followup questions:

Are cells in column F on sheet 2 merged?

When cell B5 on index sheet is cleared, should it clear only Row 7 on sheet 1 and Room 1 on sheet 2, or does clearing B5 on the index sheet clear everything on both sheets?

 

[Juddman79]

Column F on sheet 2 are merged

When Cell B5 is cleared I was hoping for the code to clear the contents of Row 7 on sheet 1 starting at G7, and to clear rows 4 (completed dates) and 5 (results) starting at J4 and J5 on sheet 2.

 

[dreid1011]

Column F on sheet 2 are merged

When Cell B5 is cleared I was hoping for the code to clear the contents of Row 7 on sheet 1 starting at G7, and to clear rows 4 (completed dates) and 5 (results) starting at J4 and J5 on sheet 2.

Okay, so sheet 1 range is G7:BE7 (every other column) and sheet 2 range is J4 to where? What is the last column on sheet 2?

Also, you've only talked about clearing the first rows on each sheet. When should the rest of the sheet be cleared?

Also, what are your sheet names?

 

[Juddman79]

Is it possible for the rest of the sheet be cleared when the date is cleared from the E column in sheet 1 and the F column in sheet 2?

On Sheet 1 when E7 is cleared every other column is cleared along row 7, when E8 is cleared every other column is cleared along row 8 and so on like this down to and including row E24 (18 rows in total).

On sheet 2, for the rest of the sheet the same sort of thing as the first row, when the date is cleared from F6 (Room 2), rows 7 and 8 clear from J7 and J8 through to DE7 and DE8. Then the same for when the date is cleared from F9 (Room 3) rows 10 and 11 clear from J10 and J11 through to DE10 and DE11, and so on to the bottom of the sheet.
On sheet 2 the last column on sheet 2 is DE and the last row is 56 (18 rooms in total each having 3 rows on the sheet).

Sheet Names are:
Sheet 1 (VBA Code 1) = Searches
Sheet 2 (VBA Code 2) = Tests

Apologies I should have been clearer in my explanation at the start.

 

[dreid1011]

Alright, see if these do what you want. All codes need to be place in the appropriate sheet modules:

Index Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Searches")
Set ws2 = Sheets("Tests")

Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("B5")) Is Nothing Then
    Set rng = ws1.Range("G7")
    For c = 7 To ws1.Range("BE7").Column Step 2
        Set rng = Union(rng, ws1.Cells(7, c))
    Next c
    rng.ClearContents
    ws2.Range("J4:DE5").ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

Sheet 1 (Searches):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim rng As Range
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("E7:E24")) Is Nothing And Target.Count = 1 And Target.Value = "" Then
    Set rng = Range("G" & Target.Row)
    For c = 7 To Range("BE" & Target.Row).Column Step 2
        Set rng = Union(rng, Cells(Target.Row, c))
    Next c
    rng.ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

Sheet 2 (Tests):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("F3:F54")) Is Nothing And Target.Count = 3 And Range("F" & Target.Row).Cells(1).Value = "" Then
    Range("J" & Target.Row + 1 & ":DE" & Target.Row + 2).ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

I assumed there were also 18 sets of 3 rows on sheet 2. If that is incorrect, adjust the Intersect Range(F3:F54) to account for the accurate rows.

 

[Juddman79]

Thanks for the coding which works, it seems to be working as you've set it up to do but I think my poor explanation of what I needed means it's not doing what I thought it would do (either that or I'm doing something wrong).


When E6 (index sheet) is cleared it clears cell E7 on sheet 1 (searches) and then every other column along row 7 in the range G7 and BE7 (searches), and then on sheet 2 (tests) clearing E6 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J4:DE5.

When E8 (index sheet) is cleared it clears cell E8 sheet 1 (searches) and then every other column along row 8 in the range G8 and BE8 (searches), and then on sheet 2 (tests) clearing E8 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J7:DE8.

When E10 (index sheet is cleared it clears cell E9 sheet 1 (searches) and then every other column along row 9 in the range G9 and BE9 (searches). and then on sheet 2 (tests) clearing E10 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J10:DE11.

and so on down to and including row E24 (18 rows in total) (index sheet) following the same pattern of cells to be cleared on sheets 1 (searches) and 2 (tests)

Thanks for your continued help with this.

 

[Juddman79]

Alright, see if these do what you want. All codes need to be place in the appropriate sheet modules:

Index Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Searches")
Set ws2 = Sheets("Tests")

Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("B5")) Is Nothing Then
    Set rng = ws1.Range("G7")
    For c = 7 To ws1.Range("BE7").Column Step 2
        Set rng = Union(rng, ws1.Cells(7, c))
    Next c
    rng.ClearContents
    ws2.Range("J4:DE5").ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

Sheet 1 (Searches):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim rng As Range
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("E7:E24")) Is Nothing And Target.Count = 1 And Target.Value = "" Then
    Set rng = Range("G" & Target.Row)
    For c = 7 To Range("BE" & Target.Row).Column Step 2
        Set rng = Union(rng, Cells(Target.Row, c))
    Next c
    rng.ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

Sheet 2 (Tests):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("F3:F54")) Is Nothing And Target.Count = 3 And Range("F" & Target.Row).Cells(1).Value = "" Then
    Range("J" & Target.Row + 1 & ":DE" & Target.Row + 2).ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub

I assumed there were also 18 sets of 3 rows on sheet 2. If that is incorrect, adjust the Intersect Range(F3:F54) to account for the accurate rows.

Thanks for the coding which works, it seems to be working as you've set it up to do but I think my poor explanation of what I needed means it's not doing what I thought it would do (either that or I'm doing something wrong).


When E6 (index sheet) is cleared it clears cell E7 on sheet 1 (searches) and then every other column along row 7 in the range G7 and BE7 (searches), and then on sheet 2 (tests) clearing E6 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J4:DE5.

When E8 (index sheet) is cleared it clears cell E8 sheet 1 (searches) and then every other column along row 8 in the range G8 and BE8 (searches), and then on sheet 2 (tests) clearing E8 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J7:DE8.

When E10 (index sheet is cleared it clears cell E9 sheet 1 (searches) and then every other column along row 9 in the range G9 and BE9 (searches). and then on sheet 2 (tests) clearing E10 (index sheet) clears the date from cell F3 that then in turn clears the contents of the cell range J10:DE11.

and so on down to and including row E24 (18 rows in total) (index sheet) following the same pattern of cells to be cleared on sheets 1 (searches) and 2 (tests)

Thanks for your continued help with this.