Assuming you do not have formulas in col C, here's a non-looping solution.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!!!
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
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 |
The non-looping macro can be a one-liner...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
[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]