Finding Cells With Only Spaces

Plase, try:

Sub testFindSpaces()
Dim wsD as Worksheet, i As Long, x As String, lastRow As Long

 Set wsD = ThisWorkbook.Sheets("data")
 lastRow = wsD.Range("A" & wsD.rows.count).End(xlUp).row
 For i = 1 To lastRow
    x = wsD.Range("a" & i).Value
    If UBound(Split(x, " ")) = Len(x) Then
        MsgBox "a" & i & " contains only space"
    End If
Next i
End Sub

Just exclude blanks by testing for Len(ThisWorkbook.Worksheets("data").Range("A" & i)) <> 0 too.

For i = 1 to lastRow
    Dim Untrimmed As String
    Untrimmed = ThisWorkbook.Worksheets("data").Range("A" & i).Value

    If Len(Trim(Untrimmed) = 0 AND Len(Untrimmed) <> 0 then
        Msgbox "a" & i & " contains only space"
    End if
Next i

Alternativeley use ThisWorkbook.Worksheets("data").Range("A" & i).Value <> vbNullString to exclude blanks

For i = 1 to lastRow
    Dim Untrimmed As String
    Untrimmed = ThisWorkbook.Worksheets("data").Range("A" & i).Value

    If Len(Trim(Untrimmed) = 0 AND Untrimmed <> vbNullString then
        Msgbox "a" & i & " contains only space"
    End if
Next i

Just to add alternatives:

With ThisWorkbook.Sheets("data").Range("A" & i)
    If .Value Like "?*" And Not .Value Like "*[! ]*" Then
        MsgBox ("A" & i & " contains only space")
    End If
End With

You may also just create a new regex-object and use pattern ^ +$ to validate the input.

If you don't want to loop the entire range but beforehand would like to exclude the empty cells you could (depending on your data) use xlCellTypeConstants or the numeric equivalent 2 when you decide to use SpecialCells() method and loop the returned cells instead:

Dim rng As Range, cl As Range

Set rng = ThisWorkbook.Worksheets("Data").Range("A:A").SpecialCells(2)
For Each cl In rng
    If Not cl.Value Like "*[! ]*" Then
        MsgBox ("A" & cl.Row & " contains only spaces")
    End If
Next cl

You may also no longer need to find your last used row, but note that this may error out if no data at all is found in column A.

A last option I just thought about is just some concatenation before validation:

For i = 1 To lastRow
    If "|" & Trim(ThisWorkbook.Sheets("data").Range("A" & i).value & "|" = "| |" Then
        MsgBox ("A" & i & " contains only space")
    End If
Next

Macro to get a string of address of cells containing only space using Evaluate VBA function

Edited code below - As suggested by @VBasic2008 and @T.M. in the comments below.

Option Explicit
Sub Cells_with_Space_Only()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
'Macro to get a string of address of cells containing only space
'https://stackoverflow.com/questions/68891170/finding-cells-with-only-spaces
Dim rngArr, rngStr As String, i As Long, rng As Range
rngArr = Evaluate("IFERROR(ADDRESS((ISBLANK(" & ws.UsedRange.Address(External:=True) & _
            ")=FALSE)*(" & ws.UsedRange.Address(External:=True) & _
            "=REPT("" "",LEN(" & ws.UsedRange.Address(External:=True) & _
            ")))*ROW(" & ws.UsedRange.Address(External:=True) & _
            "),COLUMN(" & ws.UsedRange.Address(External:=True) & ")),""**"")")
rngStr = ""

'If number of columns in usedrange are less then loop with
'For i = 1 To ActiveSheet.UsedRange.Columns.Count
For i = 1 To ws.UsedRange.Rows.Count
    'if looped with For i = 1 To ActiveSheet.UsedRange.Columns.Count
    'rngStr = Join(Filter(Application.Transpose(Application.Index(rngArr, 0, i)) _
                , "**", False, vbBinaryCompare), ",")
    
    rngStr = Join(Filter(Application.Index(rngArr, i, 0) _
                , "**", False, vbBinaryCompare), ",")
    If rngStr <> "" Then
        If rng Is Nothing Then
            Set rng = Range(rngStr)
        Else
            Set rng = Union(rng, Range(rngStr))
        End If
    End If
Next i

Debug.Print rng.Address

End Sub

The macro returns a string for the sample data in the image below -- $D$1,$A$2,$F$2,$B$3,$E$4,$A$6,$F$6,$E$7,$B$8,$D$9,$C$10,$F$10,$A$11,$D$13,$F$13,$E$14,$A$16,$E$16,$D$17,$F$17:$F$18

Array formula in the worksheet -

=IFERROR(ADDRESS((ISBLANK($A$1:$F$18)=FALSE)*($A$1:$F$18=REPT(" ",LEN($A$1:$F$18)))*ROW($A$1:$F$18),COLUMN($A$1:$F$18)),"**")

Clear Solo Spaces

• Couldn't think of any reason for doing this other than for clearing the cells containing only spaces.

Option Explicit

Sub ClearSoloSpaces()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Data")
    
    Dim srg As Range ' Source Range
    Set srg = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    
    Dim crg As Range ' Clear Range
    Dim cCell As Range ' Current Cell in Source Range
    Dim cString As String ' Current Cell's Value Converted to a String
    For Each cCell In srg.Cells
        cString = CStr(cCell.Value)
        If Len(cString) > 0 Then
            If Len(Trim(cString)) = 0 Then
                If crg Is Nothing Then
                    Set crg = cCell
                Else
                    Set crg = Union(crg, cCell)
                End If
            End If
        End If
    Next cCell
    
    If crg Is Nothing Then
        MsgBox "No cells containing only spaces found.", _
            vbInformation, "Clear Solo Spaces"
    Else
        Dim Msg As Long
        Msg = MsgBox("The cells in the rows '" _
            & Replace(crg.Address, "$A$", "") _
            & "' of column 'A' contain only spaces." & vbLf _
            & "Do you want to clear them?", _
            vbInformation + vbYesNo, "Clear Solo Spaces")
        If Msg = vbYes Then
            crg.Clear ' or crg.ClearContents ' to preserve formatting
        End If
    End If

End Sub

Just for the sake of showing alternatives (@T.M.), please test the next one, too:

Private Sub testFindSpacesBis() 
 Dim wsD As Worksheet, i As Long, x As String, lastRow As Long

 Set wsD = ActiveSheet ' ThisWorkbook.Sheets("data")
 lastRow = wsD.Range("A" & wsD.rows.count).End(xlUp).row
 For i = 1 To lastRow
    x = wsD.Range("a" & i).Value
    If StrComp(x, space(Len(x)), vbBinaryCompare) = 0 Then
        MsgBox "a" & i & " contains only spaces"
    End If
 Next i
End Sub