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]

Amend like this and it runs

Dim arr As Variant

 

[lrobbo314]

I think changing it to Variant should work.

Dim arr()   As Variant

 

[JackDanIce]

Thanks, yes it should be As Variant, I've also added test if the last row is less than 2:

Sub ClearCells()


    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 11).End(xlUp).Row
    
    If x < 2 Then End
    
    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

 

[Peter_SSs]

Is there a way to speed it up a bit?

About how many rows of data do you have?

Are there any formulas in columns E:K?

 

[The Great SrH]

Thanks, yes it should be As Variant, I've also added test if the last row is less than 2:

Sub ClearCells()


    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 11).End(xlUp).Row
    
    If x < 2 Then End
    
    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

This works perfectly, and very quickly.

Thanks so much everyone for your help!

 

[The Great SrH]

Thanks, yes it should be As Variant, I've also added test if the last row is less than 2:

Sub ClearCells()


    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 11).End(xlUp).Row
    
    If x < 2 Then End
    
    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

Sorry - I thought i was sorted.


If i wanted to extend to column M (13), i assumed the simple change would be:

x = Cells(Rows.Count, 13).End(xlUp).Row

However, this doesn't seem to work?



Thanks

 

[JackDanIce]

It's starting in column E so it's this line to change to extend to M:

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

Where column E is column index 5 and column M's index, relative to E is 9.

For count of cells between a start row and end row or start column and end column is always: End position - Start position + 1 so here, 13(M) - 5(E) + 1 = 9 (count of columns between E and M inclusive)

 

[The Great SrH]

It's starting in column E so it's this line to change to extend to M:

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

Where column E is column index 5 and column M's index, relative to E is 9.

For count of cells between a start row and end row or start column and end column is always: End position - Start position + 1 so here, 13(M) - 5(E) + 1 = 9 (count of columns between E and M inclusive)

Thanks so much for this and the detailed explanation!

 

[JackDanIce]

You're welcome, hopefully next time you'll edit the correct line!