Delete cell values based on another cell value

[JimmyC]

Sorry I am stuck on a different issue with the same project.

Again, its column B that has dates in it. The pattern is that there is two dates paired and a random number of blank cells between the pairs. For example, b1 and b2 has dates, then there is 3 blank rows {i.e. the number of blank rows between the date pairs is random} and in b6 and b7 would be the next pair of dates. This pattern continues for 12,000 rows.

What I need to do is keep the first date in the pair and erase the second date directly underneath it. In the row where the second date is located, I also need to erase the values in that same row for columns G and H.

Again, I am stuck on the logic to determine where the dates and to use the first date in the pairing to trigger the erasure of the date underneath is {i.e. clear the cell vallue} and then also clear the cells in the same row for columns G and H. This has to be a macro {I think} as all of my on-line research seems to indicate that I can not impact other cells through a formula located in one cell.

THANKS.

[HansV]

You are correct that formulas cannot delete the contents of other cells. You will need to use a macro.

- Press Alt+F11 to activate the Visual Basic Editor.
- Select Insert | Module to create a new code module to work in.
- Enter or copy/paste the following code:

Code: Select all

Sub ClearSomeCells()
  Dim r As Long
  Dim m As Long
  Application.ScreenUpdating = False
  m = Cells(Rows.Count, 2).End(xlUp).Row
  For r = 2 To m
    If Not Cells(r - 1, 2) = "" And Not Cells(r, 2) = "" Then
      Cells(r, 2).ClearContents
      Cells(r, 7).ClearContents
      Cells(r, 8).ClearContents
      r = r + 1
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

Now save the workbook so that you can go back to the current version if the macro doesn't do what you want.

You can run the code by clicking in it and pressing F5.

Alternatively, switch back to Excel.
Press Alt+F8 to display the Macros dialog.
Select ClearSomeCells in the list, then click Run.

[JimmyC]

Hans,
Thanks so much...I really need to study this one...
Jim