how to detect Space inside Excel cell VBA Excel
The code i have below works fine if the cell is empty (has had nothing entered into it and has not been editted):
If Range(sRange).Text <> "" Then
Range(sRange).Interior.Col
End If
But if there is a 'SPACE' (i.e. someone has pressed the spacebar inside the cell) or multiple spaces inside the cell then it colours it as it sees it as NOT EMPTY. How can i get around this? What code can i add into my if statement that i have posted above?
Thanks.
Picco
If Range(sRange).Text <> "" Then
Range(sRange).Interior.Col
End If
But if there is a 'SPACE' (i.e. someone has pressed the spacebar inside the cell) or multiple spaces inside the cell then it colours it as it sees it as NOT EMPTY. How can i get around this? What code can i add into my if statement that i have posted above?
Thanks.
Picco
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could use the replace function
strRange = Replace(Range(sRange).Text
if strRange = "" then
Range(sRange).Interior.Col
End If
strRange = Replace(Range(sRange).Text
if strRange = "" then
Range(sRange).Interior.Col
End If
Uhh...tony...I might be mistaking...but don't you mean:
if strRange <> "" then
Range(sRange).Interior.Col
End if
Regards,
Max.
if strRange <> "" then
Range(sRange).Interior.Col
End if
Regards,
Max.
yep sorry but you get the idea
Sure I do...just don't get why you'd replace spaces when you can do it with trim...which is meant for trimming spaces...
Regards,
Max.
Regards,
Max.
crmpicc,
do you still need more assistance with this ?
do you still need more assistance with this ?
max agreed - but users are users and i trust them about as far as I can kick them.
this question is over n I think dreammaster has the points - and I am a bit pissed so leave me alone
If length(trim(Range(sRange).
Range(sRange).Interior.Col
End If
SnowFlake