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]

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.

I see. You weren't very clear in your earlier explanations that the Index sheet controlled more than just the first row on the other two sheets. I will adjust the code and get back to you.

 

[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.

Originally you said it was cell B5 on the index sheet that controlled row E7 on sheet 1 ...

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.

Then you say it is E7 that controls row 7 on sheet 1 ...

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.

And now you're saying E6 controls row 7, E8 controls row 8, and E10 controls row 9?

Your conditions are not consistent between posts. What I am assuming you really have is E7 to E24 on the Index sheet and each one controls the same numbered row on sheet 1 and each respective section on sheet 2. Please confirm before I make any adjustments to the code. Thank you.

Index Sheet (row)	Sheet 1 (row)	Sheet 2 (rows)
E7	Row 7	Rows 3-5 (clear 4-5)
E8	Row 8	Rows 6-8 (clear 7-8)
E9	Row 9	Rows 9-11 (clear 10-11)
etc...	etc...	etc...

 

[Juddman79]

Originally you said it was cell B5 on the index sheet that controlled row E7 on sheet 1 ...

Then you say it is E7 that controls row 7 on sheet 1 ...

And now you're saying E6 controls row 7, E8 controls row 8, and E10 controls row 9?

Your conditions are not consistent between posts. What I am assuming you really have is E7 to E24 on the Index sheet and each one controls the same numbered row on sheet 1 and each respective section on sheet 2. Please confirm before I make any adjustments to the code. Thank you.

Index Sheet (row)	Sheet 1 (row)	Sheet 2 (rows)
E7	Row 7	Rows 3-5 (clear 4-5)
E8	Row 8	Rows 6-8 (clear 7-8)
E9	Row 9	Rows 9-11 (clear 10-11)
etc...	etc...	etc...

Thanks for your reply. I can confirm that the following is correct:

Index Sheet (row)	Sheet 1 (row)	Sheet 2 (rows)
E6 (E6 & E7 are merged)	Row 7	Rows 3-5 (clear 4-5)
E8 (E8 & E9 are merged)	Row 8	Rows 6-8 (clear 7-8)
E10 (E10 & E11 are merged)	Row 9	Rows 9-11 (clear 10-11)
etc...	etc...	etc...

 

[dreid1011]

Ok, here is the revised code for the Index sheet only. Do not use the codes for Sheet 1 or Sheet 2 that I provided earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long, s1Row As Long, s2Row 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("E6:E41")) Is Nothing And Target.Count = 2 And Range("E" & Target.Row).Cells(1).Value = "" Then
    s1Row = Int((Target.Row - 2) / 2) + 5
    s2Row = ((Int(Target.Row - 2) / 2) * 3) - 2

    Set rng = ws1.Range("E" & s1Row)
    For c = 7 To ws1.Range("BE" & s1Row).Column Step 2
        Set rng = Union(rng, ws1.Cells(s1Row, c))
    Next c
    rng.ClearContents

    ws2.Range("F" & s2Row - 1 & ":F" & s2Row + 1).ClearContents
    ws2.Range("J" & s2Row & ":DE" & s2Row + 1).ClearContents
End If

SetMeFree:
Application.EnableEvents = True

End Sub

 

[Juddman79]

Ok, here is the revised code for the Index sheet only. Do not use the codes for Sheet 1 or Sheet 2 that I provided earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long, s1Row As Long, s2Row 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("E6:E41")) Is Nothing And Target.Count = 2 And Range("E" & Target.Row).Cells(1).Value = "" Then
    s1Row = Int((Target.Row - 2) / 2) + 5
    s2Row = ((Int(Target.Row - 2) / 2) * 3) - 2

    Set rng = ws1.Range("E" & s1Row)
    For c = 7 To ws1.Range("BE" & s1Row).Column Step 2
        Set rng = Union(rng, ws1.Cells(s1Row, c))
    Next c
    rng.ClearContents

    ws2.Range("F" & s2Row - 1 & ":F" & s2Row + 1).ClearContents
    ws2.Range("J" & s2Row & ":DE" & s2Row + 1).ClearContents
End If

SetMeFree:
Application.EnableEvents = True

End Sub

Thanks for the code. I'll put it into the sheet when I'm back in work next week and let you know how it goes.

 

[Juddman79]

Works like a charm. Thank you for your help and patience.

 

[dreid1011]

Works like a charm. Thank you for your help and patience.

You're welcome, and happy to help.