Reputation: 51
This code will delete the entire row if it finds an empty cell in column G. What I need to do is clear the cell in column A if the cell in column G is blank.
On Error Resume Next ' In case there are no blanks
Columns("G:G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97
Upvotes: 2
Views: 1801
Reputation: 3197
Like so:
On Error Resume Next ' In case there are no blanks
Columns("G:G").SpecialCells(xlCellTypeBlanks).Offset(, -6) = ""
Upvotes: 0
Reputation: 1601
Try this instead.
Columns("G:G").SpecialCells(xlCellTypeBlanks).Offset(0,-6).ClearContents
Upvotes: 0
Reputation: 8481
Try this:
With Intersect(Columns("G:G"), ActiveSheet.UsedRange)
If WorksheetFunction.CountBlank(.Cells) > 0 Then
.SpecialCells(xlCellTypeBlanks).Offset(, -6).ClearContents
End If
End With
Upvotes: 2