Delete blank cells in range

Question

I want to delete blank cells in a range (E1:E130).

This code skips cells.

For Each cell In ranger
    If cell.Value = "" Then
        cell.Delete
    End If
next cell

To make my objective more clear: I have a list of cells with text and empty cells in the range E1:E130 and I want to make a list starting on E1 without any empty cells.
Sorting on alphabet for instance would also be a good solution, however that didn't work out for me either.

score 7 · Answer 1 · answered Mar 01 '18 at 08:59

7

I'd go like follows

With Range("E1:E130")
    If WorksheetFunction.CountA(.Cells) > 0 Then .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
End With

answered Mar 01 '18 at 08:59

DisplayName

13,283
2
11
19

score 3 · Answer 2 · answered Mar 01 '18 at 08:56

3

You could use the Range.SpecialCells Method to delete all blank cells in a specific range at once:

Range("E1:E130").SpecialCells(xlCellTypeBlanks).Delete

answered Mar 01 '18 at 08:56

Pᴇʜ

56,719
10
49
73

score 1 · Answer 3 · answered Mar 01 '18 at 08:56

You can try with this code to remove blank cell on define range :

Sub RemoveBlankCells()

Dim rng As Range

'Store blank cells inside a variable
  On Error GoTo NoBlanksFound
    Set rng = Range("E1:E130").SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0

'Delete blank cells and shift upward
  rng.Rows.Delete Shift:=xlShiftUp

Exit Sub

'ERROR HANLDER
NoBlanksFound:
  MsgBox "No Blank cells were found"

End Sub

score 1 · Answer 4 · answered Dec 19 '19 at 14:50

1

Necromancing an old question here, but: OP, I'm not sure your issue (of the 'for each cell in range' not deleting all the wanted cells) stems from the following, but before I knew about Range(...).RemoveDuplicates, i wrote 'for loops' for that very task.

At first i ran the loop from top to bottom and removed the unwanted cells. But when you remove a cell, the whole column shifts up, while your counter stays on the same value, so if there were 2 blank, the second one was shifted up when removing the first, and then the loop jumped over the blank.

So I, then, ran the loop bottom to top (step -1), and that took care of this issue.

answered Dec 19 '19 at 14:50

szhep

13
3

Do you want me to actually write a piece of code for this? I don't have one on hand, or anything, and since i've been using the .RemoveDuplicates, i have kinda forgot how i even did that. – szhep Dec 20 '19 at 11:18
Some code will help others who are less experienced than you to understand and use your answer. – Dragonthoughts Dec 20 '19 at 11:23

score 0 · Answer 5 · edited Mar 01 '18 at 09:38

0

This should work:

Columns("E:E").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

edited Mar 01 '18 at 09:38

Pang

9,564
146
81
122

answered Mar 01 '18 at 08:59

Vijay Kidecha

1
2

2

Using `.Select` is a bad practice. Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for further information. – Pᴇʜ Mar 01 '18 at 09:02
1

Thanks for correcting me.. Article was really helpful – Vijay Kidecha Mar 01 '18 at 09:15

score 0 · Answer 6 · answered May 14 '20 at 09:44

0

This one uses special methods and is more useful if you wanna erase all the data associated with the blank cells.

 Range("A:B").SpecialCells(xlCellTypeBlanks).Select
            Selection.EntireRow.Delete

answered May 14 '20 at 09:44

Alfredo Castaneda

369
3
4

Delete blank cells in range

6 Answers6

Linked

Related