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.
Delete cell values based on another cell value
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Delete cell values based on another cell value
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:
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.
- 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
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Delete cell values based on another cell value
Hans,
Thanks so much...I really need to study this one...
Jim
Thanks so much...I really need to study this one...
Jim