PDA

View Full Version : [SOLVED] Clear cells based on length of cell



austenr
06-01-2005, 05:42 PM
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
06-01-2005, 08:33 PM
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
06-01-2005, 08:34 PM
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
06-01-2005, 08:36 PM
Trying to process column A

johnske
06-01-2005, 09:56 PM
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
06-02-2005, 06:17 AM
Hi Johnske,

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

austenr
06-02-2005, 06:52 AM
I got it to work. Recorded a macro using the recorder and pieced the two together.

Solved.