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

clau818

New Member
Joined
Oct 31, 2016
Messages
32
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!!!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
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:
06842910/5/2016
06845110/11/2016
ADP000 ADP LLC (DD)
Company Code:
48293540311/11/2016
Vendor 2
Company Code:
4781959519/1/2016
48154853610/14/2016
48338322611/18/2016
Vendor 3
Company Code:
49907810/28/2016
499079 10/28/2016

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
Thanks!

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

Code:
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
 
Upvote 0
Assuming you do not have formulas in col C, here's a non-looping solution.
Code:
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...
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top