If cell length < 3, delete row. For whole column

[squidmark]

I get spreadsheets where previous users "cleared" a cell by using the spacebar. Now, I need some code that will look at all of column G and delete the rows where the cell in column G is blank. But sometimes the "blank" cell is really a space, so it just looks blank.

I've used this:
columns("G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

to delete the rows that are actually blank. Which is how I realized many aren't actually blank.

Is there a way to just say if len(g1)<2, delete whole row, but have it look at the entire column all at once as the code above does, rather than using something like a for-next loop that will take more time to run?

Yes, I'm a VBA noobie.

Thanks.

 

[squidmark]

It's an 'alternate' code for a space, although I don't know much more than that.

See if this works:

    With Columns("G")
        .Replace what:=Chr(160), replacement:="", lookat:=xlWhole
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With

Yep. That did it perfectly. Thanks Weaver!

 

[Weaver]

No problem.

Are any of your users on Apple Macs? Trying to figure out where the chr(160) got in there