VBA code on how to clear contents of 1 cell if there is data in another cell

[clau818]

So I have a spreadsheet with data in column A and data in column C. I would like to clear the cell in column A if there is any data in the adjacent cell in column C.

Thanks in advance for all of your help!!!

 

[JLGWhiz]

Sub tt()
Dim c As Range
With ActiveSheet
 For Each c In In Range("C2", .Cells(Rows.Count, 3).End(xlUp))
  If c <> "" Then
   c.Offset(, -2).ClearContents
  End If
 Next
End With
End Sub

 

[JoeMo]

So I have a spreadsheet with data in column A and data in column C. I would like to clear the cell in column A if there is any data in the adjacent cell in column C.

Thanks in advance for all of your help!!!

Assuming you do not have formulas in col C, here's a non-looping solution.

Sub ClearAifC()
Dim R As Range
On Error Resume Next
Set R = Intersect(Range("C:C"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeConstants, 7)
On Error GoTo 0
If Not R Is Nothing Then R.Offset(0, -2).ClearContents
End Sub

 

[clau818]

Hi! Thanks for the quick response!

When I tried this I get a Syntax error. I'm a VBA novice so still not quite sure what all the errors and coding mean.

Please see below for an example of my file (sorry I do not know how to post screenshots) where the vendor column is Column A and the Invoice Date column is column C. Thanks!

Vendor		Invoice Date
Vendor 1
Company Code:
068429		10/5/2016
068451		10/11/2016
ADP000 ADP LLC (DD)
Company Code:
482935403		11/11/2016
Vendor 2
Company Code:
478195951		9/1/2016
481548536		10/14/2016
483383226		11/18/2016
Vendor 3
Company Code:
499078		10/28/2016
499079		10/28/2016

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[clau818]

Yikes, sorry about the presentation of that data! I basically want to clear the contents of the cells in column A if they have a corresponding invoice date in column C

 

[clau818]

Thanks!

My data does not contain formulas but I want it to work on my entire sheet which is about 2000 rows long.

 

[JLGWhiz]

Had a typo

Sub tt()
Dim c As Range
With ActiveSheet
 For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
  If c <> "" Then
   c.Offset(, -2).ClearContents
  End If
 Next
End With
End Sub

 

[clau818]

Thanks so much! This worked!

 

[JLGWhiz]

Thanks so much! This worked!

Don't know which one you are referring to that works, but thanks for the feedback.
Regards, JLG

 

[Rick Rothstein]

Assuming you do not have formulas in col C, here's a non-looping solution.

Sub ClearAifC()
Dim R As Range
On Error Resume Next
Set R = Intersect(Range("C:C"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeConstants, 7)
On Error GoTo 0
If Not R Is Nothing Then R.Offset(0, -2).ClearContents
End Sub

The non-looping macro can be a one-liner...

[table="width: 500"]
[tr]
	[td]Sub ClearAifBhasDate()
  If Application.Count(Range("C2:C" & Rows.Count)) Then Range("C2", Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlConstants).Offset(, -2).ClearContents
End Sub[/td]
[/tr]
[/table]