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.

 

[Weaver]

you could try replacing single spaces with blanks first

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

PS, you might need a second pass with two spaces, just in case

 

[Domski]

You could use a validation rule like this to stop them just putting spaces in the cells:

=NOT(AND(LEN(A1)>0,LEN(TRIM(A1))=0))

Dom

 

[squidmark]

you could try replacing single spaces with blanks first

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

PS, you might need a second pass with two spaces, just in case

Thanks, Weaver. I used your code, and then I added another line of code for cells with two spaces, but it is still not getting rid of the cells with only a space in them. It's not replacing the spaces with clear.

The whole macro right now is:

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

I also tried to replace the spaces with "asdf" just to see if the replacement was taking place -- I changed: replacement:="" to replacement:="asdf". Is there another way to replace a space with a blank?

 

[Domski]

That should work fine although the line you've added to get rid of two spaces only has one in it.

Dom

 

[Weaver]

Thanks, Weaver. I used your code, and then I added another line of code for cells with two spaces, but it is still not getting rid of the cells with only a space in them. It's not replacing the spaces with clear.

The whole macro right now is:

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

I also tried to replace the spaces with "asdf" just to see if the replacement was taking place -- I changed: replacement:="" to replacement:="asdf". Is there another way to replace a space with a blank?

Is it possible there are non printing characters in there? This worked fine when I tried it on some test data? Maybe the space is CHAR 160, although I can't see how.

In a separate column, use =Len(G2) to find the 1 character 'empty' cells or =code(G2) to find out if it's 32 (space) or something else.

 

[Weaver]

That should work fine although the line you've added to get rid of two spaces only has one in it.

Dom

Domski, I think that might be down to the lack of code tags - html doesn't like double spaces.

 

[squidmark]

Is it possible there are non printing characters in there? This worked fine when I tried it on some test data? Maybe the space is CHAR 160, although I can't see how.

In a separate column, use =Len(G2) to find the 1 character 'empty' cells or =code(G2) to find out if it's 32 (space) or something else.

It's turning up with code 160. What is that?

 

[LCTrucido]

How big is this spreadsheet? Could you just sort the column and delete the rows manually?

 

[Weaver]

It's turning up with code 160. What is that?

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