Debug Error for macro -deleting rows

Posted on 2011-04-25
Last Modified: 2012-06-22
Why is there an error at End If ?
Previously it was working fine to delete all the unwanted rows

Basically the wanted rows would be the first row of every line.
Any row without a content in column A will need to be deleted.

Error either found in

      End If

See attached for example

Worksheet "MAR11 (t2)" is the initial worksheet
and "MAR11 (t2) (results) " is the desired final result . Macro-Test.xls
Question by:ceneiqe
    LVL 81

    Accepted Solution

    This should work better and a lot faster:

    Sub delrow()
    'DELETE ROWS THAT HAS CELLS EMPTY IN COLUMN A - for unwanted blank rows

      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Dim Rng As Range, ix As Long
      Set Rng = Intersect(ActiveSheet.Range("A:A"), ActiveSheet.UsedRange)
      For ix = Rng.Rows(Rng.Rows.Count).End(xlUp).Row To 1 Step -1
          If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
          End If
      ActiveSheet.Range(Rng.Rows(Rng.Rows.Count).End(xlUp).Offset(1), Rng.Rows(Rng.Rows.Count)).EntireRow.Delete
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
    End Sub


    Author Comment

    thanks ! so what did i do wrong ?
    still dun understand why the error occur.
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    The first thing is that the used range is huge and that made the row by row deletion take a lot longer and probably caused the error. The second thing is that I do a final delete of all rows below the last occurring non-blank column A value in one statement.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now