CODEWITHSUNDEEP
vbaexcel
Yahyaotaif
Yahyaotaif

Reputation: 1973

Delete Entire row for empty cells in VBA

I'm trying to delete every row that has empty cell for specific column which happen to be the first column in my sheet. I ran this code :

For Y = 2 To 50000
If IsEmpty(Cells(Y, 1)) = True Then
    Rows(Y).EntireRow.Delete
End If
Next Y

However, nothing changed. Did I get the code wrong somehow ?

Upvotes: 0

Views: 136

Answers (2)

Bhushan
Bhushan

Reputation: 134

You can also try this.

Sub deletecells()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim SearchRange As Range, CurrentCell As Range, DeleteMe As Range
Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

ws.Range(SearchRange.Address).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.delete
End Sub

Please let me know if your requirement is different.

Thanks

Upvotes: 0

urdearboy
urdearboy

Reputation: 14590

Change sheet name on line 3 to your sheet. This has a dynamic loop (it will loop from A2 to last used row in Column A) and round up the rows to-be-deleted. Once the loop is complete, it will delete all rows at once. 

Option Explicit

Sub DeleteMe()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

Dim SearchRange As Range, CurrentCell As Range, DeleteMe As Range
Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

For Each CurrentCell In SearchRange
    If CurrentCell = "" Then
        If DeleteMe Is Nothing Then
            Set DeleteMe = CurrentCell
        Else
            Set DeleteMe = Union(DeleteMe, CurrentCell)
        End If
    End If
Next CurrentCell

If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete


End Sub

Upvotes: 1

Related Questions