Macro to Clear data in Col C where Col B is blank

[howard]

I have a formula in Col C on sheet "Import Templates last updated" that returns a blank if the cell in the same row in Col A is blank

=IF(A21="","",LEFT(A21,SEARCH("run",A21)-2))

I have tried to write code to clear the formula in Col C that returns a blank where Col B in the same row is blank, but the code is not clearing these formulas

For e.g. if C21:C500 returns a blank then these formula are to be cleared where Col B in the same row is blank i.e empty

Sub ClearDataInColC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
   
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
   
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
   
    ' Loop through each row in Column B
    For i = 2 To lastRow ' Assuming row 1 has headers
        If IsEmpty(ws.Cells(i, "B").Value) And ws.Cells(i, "C").Formula = "" Then
            ws.Cells(i, "C").ClearContents
        End If
    Next i
End Sub

It would be appreciated if someone could amend my code

 

[eiloken]

I have a formula in Col C on sheet "Import Templates last updated" that returns a blank if the cell in the same row in Col A is blank

=IF(A21="","",LEFT(A21,SEARCH("run",A21)-2))

I have tried to write code to clear the formula in Col C that returns a blank where Col B in the same row is blank, but the code is not clearing these formulas

For e.g. if C21:C500 returns a blank then these formula are to be cleared where Col B in the same row is blank i.e empty

Sub ClearDataInColC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
  
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
  
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
  
    ' Loop through each row in Column B
    For i = 2 To lastRow ' Assuming row 1 has headers
        If IsEmpty(ws.Cells(i, "B").Value) And ws.Cells(i, "C").Formula = "" Then
            ws.Cells(i, "C").ClearContents
        End If
    Next i
End Sub

It would be appreciated if someone could amend my code

just change

If IsEmpty(ws.Cells(i, "B").Value) And ws.Cells(i, "C").Formula = "" Then

to

If IsEmpty(ws.Cells(i, "B")) And ws.Cells(i, "C").value= Empty Then

 

[RobP]

Hi,

I changed the last row column to search C instead of B (as you wont get all instances I think if you look at Col B.)

Also just changed the .formula to .value and it seems to run well .. ?

Sub ClearDataInColC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
   
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
   
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
   
    ' Loop through each row in Column B
    For i = 2 To lastRow ' Assuming row 1 has headers
        If IsEmpty(ws.Cells(i, "B").Value) And ws.Cells(i, "C").Value = "" Then
            ws.Cells(i, "C").ClearContents
        End If
    Next i
End Sub

 

[eiloken]

Hi,

I changed the last row column to search C instead of B (as you wont get all instances I think if you look at Col B.)

Also just changed the .formula to .value and it seems to run well .. ?

Sub ClearDataInColC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
  
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
  
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
  
    ' Loop through each row in Column B
    For i = 2 To lastRow ' Assuming row 1 has headers
        If IsEmpty(ws.Cells(i, "B").Value) And ws.Cells(i, "C").Value = "" Then
            ws.Cells(i, "C").ClearContents
        End If
    Next i
End Sub

Yeah, i think it will work

 

[howard]

Thanks Rob Your Solution works but very slow and thousands of rows to clear. Kindly amend code to run faster

 

[Alex Blakenburg]

very slow and thousands of rows to clear. Kindly amend code to run faster

On a copy of your workbook give this a try, it uses an array and should be quite a bit faster:

Sub ClearDataInColC_Array()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim arr As Variant
    Dim i As Long
   
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
   
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    Set rng = Range("A2:C" & lastRow)
    arr = rng.Formula
    
    On Error Resume Next        ' Cater for Evaluate failing if cell is blank or not a formula
    For i = 1 To UBound(arr)
        If Evaluate(arr(i, 3)) = "" And arr(i, 2) = "" Then arr(i, 3) = ""
    Next i
    On Error GoTo 0
    
    rng.Columns(3).Formula = Application.Index(arr, 0, 3)

End Sub

 

[RobP]

Mine was a little different to your Alex, but same principle. Runs well.

Sub ClearDataInColCFaster()

 Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
   
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
   
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
   
    formula_array = ws.Range(Cells(2, 3), Cells(lastRow, 3)).Formula
    master_array = ws.Range(Cells(2, 2), Cells(lastRow, 3)).Value
   
       
    ' Loop through each row in master_array checking for blanks
    For i = 1 To lastRow - 1 ' Array starts at row 1
        
        If master_array(i, 1) = "" And master_array(i, 2) = "" Then formula_array(i, 1) = ""
        
    Next i

    ws.Range("c2:c" & lastRow).Value = formula_array

End Sub

 

[RobP]

In fact, I've added another line here also, as I noticed if you have a value in Col B. but formula in Col C returns a blank, then it deletes the formula also.
I'm not sure if that can be a reality in your case, so use as necessary.

Sub ClearDataInColCFaster()

 Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
   
    ' Set the worksheet you want to work with
    Set ws = ThisWorkbook.Sheets("Import Templates last updated")
   
    ' Find the last used row in Column B
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
   
    formula_array = ws.Range(Cells(2, 3), Cells(lastRow, 3)).Formula
    master_array = ws.Range(Cells(2, 2), Cells(lastRow, 3)).Value
   
       
    ' Loop through each row in master_array checking for blanks
    For i = 1 To lastRow - 1 ' Array starts at row 1
        
        If master_array(i, 1) = "" And master_array(i, 2) = "" Then formula_array(i, 1) = ""
        
        'this captures in case col B has a value in, but Col C formula gives "".  Deletes formula also.
        If master_array(i, 1) <> "" And master_array(i, 2) = "" Then formula_array(i, 1) = ""
        
    Next i

    ws.Range("c2:c" & lastRow).Value = formula_array

End Sub

 

[eiloken]

Thanks Rob Your Solution works but very slow and thousands of rows to clear. Kindly amend code to run faster

With mutiple data rows, you should add: Application.displayalert=false
Application.screenupdating=false
at the beginning and:
Application.displayalert=true
Application.screenupdating=true
at the end of sub