Why is delete blank rows vba not working?

[topi1]

How come neither of the attached VBAs to delete empty rows in the attached sheet do not work? Thank you in advance for your help.

Sub Delete_empty_rows()
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Sub DeleteEmptyCellsInColumnC()
    Dim rng As Range
    Dim cell As Range

    Set rng = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)

    For Each cell In rng
        If cell.Value = "" Or cell.Value = " " Then
            cell.Delete Shift:=xlUp
        End If
    Next cell
End Sub

RapidScribe.xlsm
	C
1	ABC
2
3	LMN
4
5	XYZ
6
7	123
8	QWE
9
10	RTY
11	456
12	UIO
13
14
15	ASD
16	FGH
Sheet7

 

[SunnyAlv]

like this ?

Sub deleteblanksrow()

Dim r As Range

Set r = Range("C:C")
r.Replace What:=" ", Replacement:=""
r.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

 

[topi1]

@SunnyAlv I figured out the problem and an indirect solution. Column C has been populated by pasting values from another column which may have a formula. So some of the cells look blank but on LEN formula show 1. That is why yours and any of my vbas dont delete those rows. That also leads to a long solution. In step 1, I can use VBA like above to delete all true empty cells (rows). Also, I can have LEN(C) formula in the column B and then run another vba which delete all rows where the value in the column B is 1. But I am hoping there is a smarter and more refined way of doing this. THank you.

 

[SunnyAlv]

@SunnyAlv I figured out the problem and an indirect solution. Column C has been populated by pasting values from another column which may have a formula. So some of the cells look blank but on LEN formula show 1. That is why yours and any of my vbas dont delete those rows. That also leads to a long solution. In step 1, I can use VBA like above to delete all true empty cells (rows). Also, I can have LEN(C) formula in the column B and then run another vba which delete all rows where the value in the column B is 1. But I am hoping there is a smarter and more refined way of doing this. THank you.

Does column C have a header? I think if it does you can add a Filter to column C, then select the "(Blanks)" criteria, then select the row and delete the row.

 

[topi1]

I tried that. It does not think these cells are blank.

 

[topi1]

I think the solution may be in changing it to range or something.

 

[SunnyAlv]

@topi1 i just try it and works perfect if Column C have a Header

Sub DeleteEmptyCellsInColumnC()
    Dim rng, rng2 As Range
    Dim cell As Range

    Set rng = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rng2 = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)

rng2.Select
rng.AutoFilter 1, "="
Selection.EntireRow.Delete
rng.AutoFilter

End Sub

 

[topi1]

Very odd. Leaves blank for me. Crazy.

 

[SunnyAlv]

Very odd. Leaves blank for me. Crazy.

The VBA I provided works fine for me even when column C has a formula, okay I think someone can help you get a better answer.

 

[topi1]

Thank you so much for all your time and help. Will revisit tomorrow fresh. TY.