CODEWITHSUNDEEP
vbaexcel
warasen
warasen

Reputation: 51

Clear cell in row if another cell in the same row is blank

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

Answers (3)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

Like so:

On Error Resume Next     ' In case there are no blanks
Columns("G:G").SpecialCells(xlCellTypeBlanks).Offset(, -6) = ""

Upvotes: 0

mischab1
mischab1

Reputation: 1601

Try this instead.

Columns("G:G").SpecialCells(xlCellTypeBlanks).Offset(0,-6).ClearContents

Upvotes: 0

Reafidy
Reafidy

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

Related Questions