VBA check if active sheet has values that starts or ends with space

[Kra]

Hi all!

I have a dataset of over 50k rows in excel. Some of values starts or ends with space and it is incredibly annoying to maintain this kind of data. I keep using TRIM formula but it slows down sheets with lots of data.

I tried to use code to highlight cells with spaces before or after value, but it is not working. It highlights values without spaces. Any idea how to fix it?

Is it also possible to adjust it so it checks whole sheet, not only selection and it shows msg box at the end with information how many cells with spaces were found?

Code I am using:

Private Sub CommandButton19_Click()

    Dim i As Long, j As Long
    Dim sh As Worksheet
    Dim sheetArr As Variant
    Set sh = ActiveSheet
    sheetArr = sh.UsedRange
    rowC = sh.UsedRange.Rows.Count
    colC = sh.UsedRange.Columns.Count
    For i = 1 To rowC
        For j = 1 To colC
            If Left(sheetArr(i, j), 1) = " " Then
                sh.Cells(i, j).Interior.ColorIndex = 37
            End If
            If Right(sheetArr(i, j), 1) = " " Then
                sh.Cells(i, j).Interior.ColorIndex = 37
            End If
        Next j
    Next i


End Sub

Result in sheet (column D selected):

Book1
	A	B	C	D	E	F
1
2
3				No Space
4				No Space
5				Space After
6				Space Before
7
8
9
10
11
12
Sheet1

 

[Automatrix]

Power Query --> Right click on column --> Trim. Refresh query as needed.

 

[Kra]

Power Query --> Right click on column --> Trim. Refresh query as needed.

Thank you, but it is important to me to just highlight these cells and count them, not to trim them - it will be done as next step.

 

[Automatrix]

Power Query --> Duplicate column --> Right click on new column --> Trim --> New column: length of original column --> New column: length of trimmed column --> conditional column: the two lengths don't match = specific text.

 

[Kra]

Power Query --> Duplicate column --> Right click on new column --> Trim --> New column: length of original column --> New column: length of trimmed column --> conditional column: the two lengths don't match = specific text.

Thanks! It works, but I would love to have it implemented by macro, so it can be done in one click only and I will see msg box with counter