Problem with code for Clearing Content in Column Using User Input

Hi,

Problem: The code runs, but does not clear the value entered in the inputbox, not sure what's wrong

This works if I take out the DelValue in this line and insert the value such as

[TABLE="width: 70"]

[TD="width: 70, align: right"]200007649[/TD]

[/TABLE]

If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents

The code runs without errors, but does not clear the contents

Sample Data for col 2

[TABLE="width: 70"]

order_nr

[TD="align: right"]200007649[/TD]

[TD="align: right"]200008649[/TD]

[TD="align: right"]200009649[/TD]

[TD="align: right"]200006649[/TD]

[TD="align: right"]200006649[/TD]

[TD="align: right"]200004649[/TD]

[/TABLE]

Thank You in Advance
James

Private Sub CommandButton1_Click()


' This will delete specific value in column


Dim i As Integer
Dim ColCount As Integer
Dim DelValue As Variant


DelValue = InputBox("Value to Delete?")
ColCount = Application.WorksheetFunction.CountA(Columns(2))




For i = 1 To ColCount


If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents


Next i


End Sub

Re: Problem with code for Clearing Content in Column Using User Input

Hi jproffer.

Thanks for the reply. That didn't work.. It's quite baffling why the code works if I explicitly put in the value, yet the Variable doesn't work.
Other suggestions would appreciate it.
James.

Quote from jproffer;603958

CountA only counts non-blank cells, so if there are blanks in that column the loop won't go all the way to the bottom. That may not be an issue in this case, but try this and see:

Code

Private Sub CommandButton1_Click()
     
     ' This will delete specific value in column
     
    Dim i As Integer
    Dim ColCount As Integer
    Dim DelValue As Variant
     
    DelValue = InputBox("Value to Delete?")
    ColCount = Range("B" & Rows.Count).End(xlUp).Row
     
     
    For i = 1 To ColCount
         
        If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents
         
    Next i
     
End Sub

Display More

If that don't work, post your workbook and someone might see something else causing your troubles.