CODEWITHSUNDEEP
vbaexcel
user7444699
user7444699

Reputation:

How to delete empty cells in excel using vba

This is just a sample I am testing the code in this data. I have three columns in sheet2. I have to delete the empty cells. This is the updated code which is working for column B only. You can check the snapshot

   Sub delete()
   Dim counter As Integer, i As Integer
    counter = 0

  For i = 1 To 10
    If Cells(i, 1).Value <> "" Then
        Cells(counter + 1, 2).Value = Cells(i, 1).Value
        counter = counter + 1

    End If
Next i
End Sub

Sample screenshot
enter image description here

Upvotes: 0

Views: 7198

Answers (2)

MortenAnthonsen
MortenAnthonsen

Reputation: 78

Not the most elegant solution but it works.

Option Explicit
Sub delete()
Dim rCells As Range, rCell As Range, sFixCell As String

Set rCells = Range("A1:A13")
For Each rCell In rCells
    If rCell = "" Then
        sFixCell = rCell.Address
        Do While rCell.Value = ""
        rCell.delete Shift:=xlUp
        Set rCell = Range(sFixCell)
        Loop
    End If
Next rCell

End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

If all you want is to delete the empty cells, give this a try...

Sub DeleteBlankCells()
Dim rng As Range
On Error Resume Next
Set rng = Intersect(ActiveSheet.UsedRange, Range("A:C"))
rng.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End Sub

Upvotes: 3

Related Questions