VBA code to clear contents of cell A and B if cells C blank when macro runs from another sheet

Question

I am trying to clear extra cells of column A and B if find blank cells in column C of sheet named Sold and Macro runs from Sheet named Invoice.

I got below code from a helping site but cannot get my desired result.Below attached is the screenshot of desired output

Sum Clear()

Dim g As Long
For g = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(g, "C").Value = "" Then
Cells(g, "A").ClearContents
Cells(g, "B").ClearContents 

End If
Next
End Sum

I think either AutoFilter or SpecialCells would save you from any iteration. — JvdV, Feb 12 '20 at 07:41

score 0 · Answer 1 · answered Feb 12 '20 at 06:22

0

You need to include the name of the sheet.

Sub Clear()
Dim g As Long
With ActiveWorkbook.Sheets("Sold")
    For g = 2 To .UsedRange.Rows.Count
        If .Cells(g, "C").Value = "" Then
            .Cells(g, "A").ClearContents
            .Cells(g, "B").ClearContents
        End If
    Next
End With
End Sub

answered Feb 12 '20 at 06:22

This work fine for me, i got that point. You save me. Have Good Day – lucky1987 Feb 13 '20 at 07:48

score 0 · Answer 2 · answered Feb 12 '20 at 06:59

Non VBA Way

you can put formula =IF(LEN(TRIM(C2))=0,"",POPULATETHISCELL) in the cells A and B where you can replace POPULATETHISCELL with whatever you are trying to get there. For example, populating today's date.

VBA Way

Work with Objects. Avoid the use of ActiveWorkbook/Activesheet/Selection etc as show HERE
Avoid the use of UsedRange. Find last row as shown HERE and then loop through it.
Instead of clearing ranges twice, you can clear it in one go as shown below

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sold")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            For i = 2 To lastrow
                If Len(Trim(.Range("C" & i).Value)) = 0 Then _
                .Range("A" & i & ":B" & i).ClearContents
            Next i
        End If
    End With
End Sub

This work fine for me, Thanks for Immediate feedback. You save me. Have Good Day — lucky1987, Feb 13 '20 at 07:49

score 0 · Accepted Answer · answered Feb 12 '20 at 07:33

Try something like :

Sub Sample()
 '============ Init ================
 Dim Ws_Sold As Worksheet
 Set Ws_Sold = Sheets("Sold")
 Dim count As Integer
 count = 1
 Dim lastrow As Long
 lastrow = Ws_Sold.Cells.SpecialCells(xlCellTypeLastCell).Row 
 'give number of the last cell not empty

 '============ loop================
 While (count < lastrow + 1)
    If Ws_Sold.Cells(count, 3).Value = "" Then
        Ws_Sold.Cells(count, 1).ClearContents
        Ws_Sold.Cells(count, 2).ClearContents    
    End If
    count = count + 1
 Wend

End Sub

Have a nice day !

It worked for me fine, Thanks for Immediate feedback and co-operate. You save my day. Have a Nice Day Dear — lucky1987, Feb 13 '20 at 07:51

VBA code to clear contents of cell A and B if cells C blank when macro runs from another sheet

3 Answers3