• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Debug Error for macro -deleting rows

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
Rng.Item(ix).EntireRow.Delete

Or
      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
0
ceneiqe
Asked:
ceneiqe
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
        Rng.Item(ix).EntireRow.Delete
      End If
  Next
  ActiveSheet.Range(Rng.Rows(Rng.Rows.Count).End(xlUp).Offset(1), Rng.Rows(Rng.Rows.Count)).EntireRow.Delete
 
done:
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Kevin
0
 
ceneiqeAuthor Commented:
thanks ! so what did i do wrong ?
still dun understand why the error occur.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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.

Kevin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now