VBA - Clear cell contents in range if less than 5 characters

[The Great SrH]

Hi,


I'm hoping somebody can help with a code to clear the contents of a cell in a range, if that cell contains less than 5 characters (it may be a "." or "yes", etc.)

Starting in row 2, Columns E to K will contain an extract of comments. However, some cells contain a full stop, "N/A" etc which we don't require.


I would like a macro which will only search those columns, and clear the contents if less than 5 characters.


Thanks in advance

 

[Yongle]

Try something like this

Sub ClearCells()
    Dim cel As Range
    For Each cel In Range("E2:K5000")
        If Len(cel.Text) < 5 Then cel.ClearContents
    Next cel
End Sub

You may prefer to determine the last row based on last cell containing value in a column (here using column A)

Dim r As Long
r = Range("A" & Rows.Count).End(xlUp).Row

For Each cel In Range("E2:K" & r)
etc

 

[The Great SrH]

Try something like this

Sub ClearCells()
    Dim cel As Range
    For Each cel In Range("E2:K5000")
        If Len(cel.Text) < 5 Then cel.ClearContents
    Next cel
End Sub

[/CODE]

Thanks for this!

It works - it's just very slow. Is there a way to speed it up a bit?

 

[JackDanIce]

Try:

Sub ClearCells()

    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Long
    
    x = Cells(Rows.Count, 11).End(xlUp).Row
    arr = Cells(2, 5).Resize(x - 1, 7).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            If Len(arr(x, y)) < 5 Then arr(x, y) = vbNullString
        Next y
    Next x
    
    Cells(2, 5).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
    Erase arr
    
End Sub

 

[The Great SrH]

Try:

Sub ClearCells()

    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Long
    
    x = Cells(Rows.Count, 11).End(xlUp).Row
    arr = Cells(2, 5).Resize(x - 1, 7).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            If Len(arr(x, y)) < 5 Then arr(x, y) = vbNullString
        Next y
    Next x
    
    Cells(2, 5).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
    Erase arr
    
End Sub

Thanks Jack - i havent tried it yet, i just wanted to double check what the sections mean?
Does this only clear contents in columns E to K?

 

[JackDanIce]

Cells(2, 5) is row 2 column 5 (E2)
x is the row number of the last row with data in column K (11)

Data is read into an array in range E2:Kx (don't worry about the resize part)

The array is looped to test values of length less than 5 and cleared if so, the results are printed back to E2:Kx

 

[The Great SrH]

Cells(2, 5) is row 2 column 5 (E2)
x is the row number of the last row with data in column K (11)

Data is read into an array in range E2:Kx (don't worry about the resize part)

The array is looped to test values of length less than 5 and cleared if so, the results are printed back to E2:Kx

Thanks Jack - I've just tried and I'm getting a "Type mismatch" on this row:

arr = Cells(2, 5).Resize(x - 1, 7).Value

 

[Ramballah]

i think he made a mistake with his dims

Dim arr()    As Long

but it should be

Dim arr    As Long

 

[lrobbo314]

i think he made a mistake with his dims

Dim arr()    As Long

but it should be

Dim arr    As Long

No, because it is an array, not a single Long variable.

 

[The Great SrH]

Thanks both - @Irobbo314, whats the solution?