Clear cell

[Panoos64]

Hi everyone, i would like to write a vba so that to run through col. "A" and where find the text Grand Total should clear the contents in adjacent cell in col. "B" as my below extract



Original Data

A B
Grand Total Debtor Name



Expected result

A B
Grand Total

 

[NdNoviceHlp]

Sub test2()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("Sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
If Sheets("Sheet1").Range("A" & Cnt).Value = "Grand Total" Then
Sheets("Sheet1").Range("B" & Cnt).Value = vbNullString
End If
Next Cnt
End Sub

HTH. Dave

 

[Panoos64]

Hi Nd, I am sorry, but i have to say that it doesn't work and no error message appear. If you would like to adjust it, i am kindly require to arrange it through "Active Sheet" and not for a specific "Sheet1". However thanks for your support and your time spent for my project. Have a nice day

 

[Rick Rothstein]

Give this macro a try...

Sub ClearNextToGrandTotal()
  With Columns("A")
    .Replace "Grand Total", "#N/A", xlWhole, , False, , False, False
    With Columns("A").SpecialCells(xlConstants, xlErrors)
      .Offset(, 1).ClearContents
      .Value = "Grand Total"
    End With
  End With
End Sub

 

[Tetra201]

Or this one:

Sub ClearBGT()
    Dim LastRow As Long, Addr1 As String, Addr2 As String
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Addr1 = "A1:A" & LastRow
    Addr2 = "B1:B" & LastRow
    Range(Addr2) = Evaluate("IF(" & Addr1 & "=""Grand Total"",""""," & Addr2 & ")")
End Sub

 

[Panoos64]

Thank you Rick, it works perfect and according to my require. Have a great day!

 

[Tetra201]

@ Rick Rothstein:

Just for fun: 10,000 rows, 2,500 "Grand Total", your code 0.125 seconds, my code 0.0156 seconds.

 

[Panoos64]

Hi Tetra, the code works and it clear the data to adjacent cell of the Grand Total. The problem which occur is where in Col. "A" is blank, it appears in adjacent cells in col "B" 0 (zero). However i would like to thank you for your support. Have a nice day!

 

[Tetra201]

Thank you for spotting this -- acknowledged and fixed:

Sub ClearBGT()
    Dim LastRow As Long, Addr1 As String, Addr2 As String
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Addr1 = "A1:A" & LastRow
    Addr2 = "B1:B" & LastRow
    Range(Addr2) = Evaluate("IF((" & Addr1 & "=""Grand Total"")+(" & Addr2 & "=""""),""""," & Addr2 & ")")
End Sub

 

[Panoos64]

I express my apologies Nd. It works perfect your code. I was testing the code with wrong data. Many thanks for your support and i am apologizing once again. Have a great day!