Visual Basic Macro to Delete Blank Rows

Your rating: None Average: 1.5 (2 votes)

Users love "copy & past" from one excel document to another. This however can result in having some empty rows in the document and could cause some major headache if you are trying to generate a .txt file and upload it for processing.

One way around this is to create an excel macro that will delete blank rows once the user generates the .txt file.

Here is the code snippet:

'Deletes blank rows before creating the TAB delimited file
   Dim R As Long
    Dim C As Range
    Dim Rng As Range

    On Error GoTo EndMacro
    Application.ScreenUpdating = False

    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = ActiveSheet.UsedRange.Rows
    End If
    For R = Rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
            Rng.Rows(R).EntireRow.Delete
        End If
    Next R

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Stefan's picture
Stefan (not verified)
It works!!! Nice code.

It works!!! Nice code. Thanks.

CompShack's picture
User offline. Last seen 4 years 38 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Super

Hi Stefan,
Great to hear you put the code for good use :)

n/a