Clear cells based on length of cell

[austenr]

I have looked all over for this but cannot find it. How do you clear a range of cells based on it being over a certain length? :(

[austenr]

Sub ClearCells()
Dim Acell As Range
For Each Acell In Range("A1:A65500")
If Len(Acell) > 20 Then Acell = " "
Next Bcell
End Sub

What is wrong with this statement?

[austenr]

Sub ClearCells()
Dim Acell As Range
For Each Acell In Range("A1:A65500")
If Len(Acell) > 20 Then Acell = " "
Next Acell
End Sub

Corrected Next

[austenr]

Trying to process column A

[johnske]

Hi Austen,

Try this, it'll be faster, but to be really fast you'd need to use the Find function


Sub ClearCells()
Dim Acell As Range, LastRow As String
LastRow = Columns(1).Rows(Rows.Count).End(xlUp).Address
Application.ScreenUpdating = False
For Each Acell In Range("A1:" & LastRow)
If Len(Acell) > 20 Then Acell.ClearContents
Next
End Sub


HTH,
John :)

[austenr]

Hi Johnske,

Thanks for the code. I cannot seem to get it to work however. Attached is my sample code.

[austenr]

I got it to work. Recorded a macro using the recorder and pieced the two together.

Solved.