Delete entire row in excel if column A is empty

[bpremlatha]

I'm very new in using VBA. The excel table has more than 500 rows. I would like to run vba code to remove row where column A is empty. I tried run this code.

Sub DeleteAlternateRow()
    Dim i As Long
    Dim DelRange As Range

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    For i = 1 To 1500
        If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "DG" & i)) = 0 Then
            If DelRange Is Nothing Then
                Set DelRange = rows(i)
            Else
                Set DelRange = Union(DelRange, rows(i))
            End If
        End If
    Next i

    If Not DelRange Is Nothing Then DelRange.delete shift:=xlUp
LetsContinue:
    Application.ScreenUpdating = True

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

It did remove the rows as I wanted. But, starting from column 31 and row 54, the field content goes to next field.How do I solve? Please help me. Thank you.

[twinnyfo]

bpremlatha,

First, since your image only shows two rows, neither of which would appear to satisfy the conditions of your code, it is unclear what that is trying to demonstrate. Or, is the image related to your comment:

But, starting from column 31 and row 54, the field content goes to next field.

?

In that case, I can't specifically tell you what is going wrong. However, I would advise the following:

Instead of consolidating the ranges, and deleting at the end, why not delete rows as you go along?

One of your challenges is that you are setting your counter to run from 1 to 1500 (to cover as many rows as you think you might need, which, in the case above, you would be caught in an infinite loop deleting empty rows. In this case, you might want to find the last row of populated data first (if your data is in a set order, such that there is always only one empty row between records, then, when you find two empty rows in consecutive order, you know you are at the end. Then, instead of working your way down your list, you would work your way up.

A little more complex, but it would allow you to see what was happening with each particular row as it was deleted so that you could better troubleshoot at that point.

Hope this made sense--but even more, I hope it helps.