findRegex
option explicit
sub findRegex(pattern as string) ' {
dim c as range
dim reWS as new regexp
reWS.pattern = pattern
for each c in activeSheet.usedRange
if reWS.test(c.value) then
debug.print c.address
' c.value = ""
end if
next c
end sub ' }
In order to be able to refer to the regexp object models by name, the following reference needs to be added in the workbook:
thisWorkbook.VBProject.references.addFromGuid GUID:="{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", major := 5, minor := 5
findWhitespaces
findWhitespaces is a function that uses
findRegex with the regular expresion
[ \t\n\r]+ to uses finds cells that contain only spaces, tabs and/or
new lines.
This function comes handy to reduce the size of
usedRange if there are some cells with whitespace lingering.
sub findWhitespaces() ' {
findRegex "[ \t\n\r]+"
activeSheet.usedRange.select
end sub ' }