I am trying to find any cells with just spaces in. When I run this though it finds cells that are blanks too. Is there anyway to just find cells with spaces?
For i = 1 to lastRow
If len(trim(this workbook.sheets("data").range("a" & i)) = 0 then
Msgbox("a" & i " contains only space")
End if
Next i
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)),"**")
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
