Why is delete blank rows vba not working?

topi1

Active Member
Joined
Aug 6, 2014
Messages
252
Office Version
  1. 2010
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.

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

Rich (BB code):
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
1ABC
2
3LMN
4
5XYZ
6
7123
8QWE
9
10RTY
11456
12UIO
13
14
15ASD
16FGH
Sheet7
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
like this ?

VBA Code:
Sub deleteblanksrow()

Dim r As Range

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

End Sub
 
Upvote 0
@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.
 
Upvote 0
@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.
 
Upvote 0
I think the solution may be in changing it to range or something.
 
Upvote 0
@topi1 i just try it and works perfect if Column C have a Header

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
Thank you so much for all your time and help. Will revisit tomorrow fresh. TY.
 
Upvote 0

Forum statistics

Threads
1,223,939
Messages
6,175,530
Members
452,651
Latest member
wordsearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top