CODEWITHSUNDEEP
excelvbacell
Boolean
Boolean

Reputation: 99

Delete blank cells in range

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.

Upvotes: 4

Views: 38126

Answers (6)

Alfredo Castaneda
Alfredo Castaneda

Reputation: 409

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

Upvotes: 0

szhep
szhep

Reputation: 13

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.

Upvotes: 1

Vijay Kidecha
Vijay Kidecha

Reputation: 1

This should work:

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

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

I'd go like follows

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

Upvotes: 7

Léo R.
Léo R.

Reputation: 2698

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

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

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

Upvotes: 3

Related Questions