CODEWITHSUNDEEP
vbaexcelexcel-formula
Mike
Mike

Reputation: 155

Count cells containing Chr(160) character in VBA

I use the following code to remove all Chr(160) characters, i.e. the non-breaking spaces which occur occasionally after pasting manually (depending on the source of copying).

This works fine so far:

Worksheets(2).Range("A:A").Replace Chr(160), "", xlPart

How can I count the number of cells containing this character before removing it? I tried the following code for this but unfortunately that's not working:

varBlankCount = Application.WorksheetFunction.CountIf(Range("A:A"), "*" & Chr(160) & "*")

Upvotes: 1

Views: 580

Answers (1)

R3uK
R3uK

Reputation: 14537

Always specify the sheet you want to work with :

varBlankCount = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), "*" & Chr(160) & "*")

Upvotes: 2

Related Questions