VBA code to clear contents of cell A and B if cells C blank when macro runs from another sheet
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
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
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
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
B where you can replace POPULATETHISCELL with whatever you are trying to get there. For example, populating today's date.
- Work with
Objects. Avoid the use of
ActiveWorkbook/Activesheet/Selectionetc 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
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 Next 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