Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel macro to delete a row (excel xp)

    I wrote this macro to look in Column A and if the cell is blank (NULL) then delete the whole row. This is the only way i could figure out how to do it. It runs very SLOOOOOw. Is there a way i can make this much faster?

    Public Sub xjob()
    Dim rng As Range
    Dim rngvalue As Variant


    ScreenUpdating = False
    'lRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row

    Range("A1").Select
    For I = 1 To 400
    rngvalue = Range("A" & I).Value
    If rngvalue = "" Then
    Range("A" & I).EntireRow.Delete
    I = I - 1
    End If
    Next I

    ScreenUpdating = True


    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro to delete a row (excel xp)

    Try the following:

    <pre>Public Sub DelNull()
    Dim lLastRow As Long, I As Long
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
    lLastRow = .Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    If .Range("A1").Offset(I, 0).Value = "" Then
    .Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End With
    Application.ScreenUpdating = True
    End Sub
    </pre>


    Your routine will also miss rows when there are more than one null cell in a row.
    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel macro to delete a row (excel xp)

    Another thing that might help includes switching off recalc at the start of the macro, then switching it back on at the end-
    Application.Calculation = xlManual
    ' main routine goes here
    Application.Calculation = xlAutomatic

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel macro to delete a row (excel xp)

    Thank you so much! This is perfecto

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •