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

Related searches

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
End Sum

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

End Sub

Have a nice day !

How to clear specified cell contents if value of another cell changes , Clear specified cell contents if value of another cell changes with VBA code In the worksheet you will clear cell contents based on another cell changes, right A and B if find blank cells in column C of sheet named "Sold" and Macro runs� VBA Clear Contents can clear data from a cell or given cell range. Clear contents only clear the data from the cells it does not affects the formatting of the cells. Even if the data is in conditional formatting, clear contents do not clear the formatting of the cells. Recommended Articles. This is a guide to VBA Clear Contents.

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.


  1. Work with Objects. Avoid the use of ActiveWorkbook/Activesheet/Selection etc as show HERE
  2. Avoid the use of UsedRange. Find last row as shown HERE and then loop through it.
  3. 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, _

            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

Delete cells by using a For Each Next loop, Microsoft Excel 2002 and later versions of Excel delete cells in a "For Each. and it provides a Visual Basic for Applications macro example that A1: a B1: 1 A2: b B2: 2 A3: x B3: 3 A4: x B4: 4 A5: c B5: 5 A6: x B6: 6 A7: Count 'If cell i in the range contains an "x", 'delete the row. View all page feedback. VBA code: Clear specified cell contents if value of another cell changes. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2")) Is Nothing Then Range("C1:C3").ClearContents End If End Sub. Note: In the code, B2 is the cell you will clear cell contents based on, and C1:C3 is the range you will clear contents from. Please change them as you need.

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
End With
End Sub

Excel VBA Clear Cell: Step-by-Step Guide and 5 Examples, VBA Code to Clear Cell Contents and Keep Formatting; Process Followed by VBA to Learn how to delete blank or empty rows here. If you explicitly declare an object variable to represent Cells, use the Range object data type. in the worksheet named “Clear Cell” of the workbook containing the macro ( ThisWorkbook). The results of the macro are as follows: A1: a B1: 1 A2: b B2: 2 A3: c B3: 5 A4: d B4: 7 A5: e B5: 10 When row 3 is deleted, all cells move up one row. Then, cell A3 assumes the contents of cell A4, cell A4 assumes the contents of cell A5, and so on.

VBA Macro to Delete Rows Based on Cell Values or , To remove the rows that have blank cells like this one, the macro first applies Activate 'not required but allows user to view sheet if warning message appears of cells, you can still delete rows based on cell contents using a macro. a Yes /No message box before the macro runs for details on this code. It searches and selects blank cells and then deletes the entire row which contains a blank cell. Code explanation. Set Rng = Range("A9:C" & LastRow) The above code is used to create a range object for the given data. Rng.SpecialCells(xlCellTypeBlanks).Select. The above code is used to select blank cells within the specified range.

Excel VBA IF THEN Statement (and a useful tip) - Xelplus, Excel VBA IF THEN Statement is one of the most useful statements in VBA. We also take a look at looping through a range of cells using Excel table referencing. Another test could be to test the value of a cell, such as “Is the cell value greater clear the contents of cell C9 and re-run the macro, cell C9 will remain blank. I am using below code to clear contents from A2:H2 rows except the first one where I have the header. This code works well if there are no blank cells in between. But how can I clear everything eventhough there is a blank cell?

Can we clear excel data of two excel sheet of specific cell’s in one code . e.g. i have two excel sheet in my workbook A and B and i wont clear data of specific cell using range option (A3:B10) for Sheet A and (C2:D10) for sheet B, i want this operation in One code only can you please help me . Regards . Dushyant Padhya

  • I think either AutoFilter or SpecialCells would save you from any iteration.
  • It worked for me fine, Thanks for Immediate feedback and co-operate. You save my day. Have a Nice Day Dear
  • This work fine for me, Thanks for Immediate feedback. You save me. Have Good Day
  • This work fine for me, i got that point. You save me. Have Good Day