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

 

[Peter_SSs]

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.

What is the exact formula in the other column?

 

[MARK858]

Try using the code below before the delete as long as you do need no formulas in the column

Range("C:C").Value = Range("C:C").Value

 

[topi1]

What is the exact formula in the other column?

@Peter_SSs the formula are not always same. Could be direct reference to a same row cell from another column, vlookup etc.

Since it says Len(c)=1, and not expecting one character in the C and keeping B empty, I came up with the following code. It works. A combination as you can tell. Refined but it seems to work. if all fails, I could it. Thank you.

Sub DELETEALLBLANKROWS_()
  On Error Resume Next
  Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

   Dim rng As Range
    Dim cell As Range

    ' Define the range for cells B1 to B250
    Set rng = Range("B1")

    ' Iterate through each cell in the defined range
    For Each cell In rng
        ' Add the desired formula to each cell
        cell.Formula = "=len(c1)"
    Next cell

Range("B1").Select
    Selection.Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("B1:B250"), Type:=xlFillDefault
    Range("B1:B250").Select


    Dim i As Long
    ' Start from the last cell and move upwards to prevent skipping cells after deletion
    Set rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    For i = rng.Cells(rng.Cells.Count).Row To 1 Step -1
        Set cell = rng.Worksheet.Cells(i, "B")
        If cell.Value = 1 Then
            cell.EntireRow.Delete
        End If
    Next i
    
    Columns("B:B").Select
    Selection.ClearContents
    Range("B1").Select

End Sub

 

[Peter_SSs]

Try using the code below before the delete as long as you do need no formulas in the column

Range("C:C").Value = Range("C:C").Value

That won't help if this is actually true ..

So some of the cells look blank but on LEN formula show 1.

 

[topi1]

Try using the code below before the delete as long as you do need no formulas in the column

Range("C:C").Value = Range("C:C").Value

@MARK858 you had educated me about this in the prior posting and I had tried it. Tried again. It did not work.

 

[topi1]

That won't help if this is actually true ..

Correct. It did not help.

 

[Peter_SSs]

@Peter_SSs the formula are not always same.

Well it sounds like the original formulas could be returning " " rather than "" otherwise the LEN would not be 1. First thing I would do is check that and change the formulas so they are retuning a zero length string rather than a space character. Then include the extra line Mark has suggested.

 

[MARK858]

That won't help if this is actually true

Teach me for reading it on my phone

 

[topi1]

That won't help if this is actually true ..

It is derived from a shared file. Perhaps, I can use a substitute formula to achieve what you have suggested. Will try. For now, got to run. I really appreciate the help!

 

[topi1]

still trying to figure out what is going on. Meanwhile comforted by the fact that my convoluted vba works. Thanks.