Delete Rows in VBA

Posted by Shamsuddeen. P.K on January 07, 2002 10:47 AM

I have downloaded and formatted a text file to excel thru VBA macro. The file contains more than 100 records. Column H contains Total of colums A to G.

I want to delete all the records (with the help of a macro) whose Totals are 0 (cloumn H). I have tried the following macro which deletes most of the rows but some are left undeleted.

For i = 1 to LastRow ' LastRow = Total No. of records

If cells(i,8).value = 0
Range("H" & i).EntireRow.Delete
endif
Next

Plese assist

Regards,

Shamsuddeen

Posted by Gary Bailey on January 07, 2002 11:44 AM

Try

For i = LastRow to 1 Step -1

Gary

Posted by Jack in UK (not qnswer good tip) on January 07, 2002 2:16 PM

Hi Shamsuddeen
I dont want to answer as Gary has it spot on, but in worried about not deleting ALL rows?

Why? I feel the code will work no matter what but i email help tips arround the world, and few days back beleive it or not i found th need to tell people about CELLS, full empty and used or unused and to explain the difference, you comments struck a cord, poss in wrong but just in case

Just once select autofilter on all the H Column and filter to custon "" select the reange right ck click and DO NOT DELETE but clear contence, now reset autofilter to all and run code, see if it now picks up all blank H Column and delets them, now autofilter to BLANKS and select range if any left and right click and clear contence, reset auto filter to all and run code, see if your cleear of blank H row data if so

You are not clearing (Clearing) data from the cells somehow, i never delet, always reset (Clear contence)

Try it just in case woth the though i felt
Hope its of some help
Rdgs
Jack

Posted by Shamsuddeen. P.k on January 08, 2002 9:13 AM

Delete Rows - Thanks - Problem Solved and Discovered the Reason

Thank you Mr. Gary. It really solved my problem. Also many thanks to Mr. Jack for the tips he posted.

I discovered the reason why my macro leaves some of the rows undeleted.

Assume, the Totals of Row No. 6 and 7 are 0s. While the macro processing the records from top to bottom, first it deletes the Row No. 6. Since the 6th row has already been deleted, Row No. 7 is pushed up and it becomes row no. 6. By the time the loop has incremented to 7 and it does not process row no. 6 (previously 7) again and hence it leaves the row undeleted and continues to process the next record. But if the loop is reversed this problem does not occur.

Regards,

Shamsuddeen

Posted by Menas on January 08, 2002 7:30 PM

Alternatively .......

.... here's some more efficient code (avoids using a loop) :-

Columns("I:I").Insert
With Intersect(ActiveSheet.UsedRange, Columns("H:H")).Offset(0, 1)
.FormulaR1C1 = "=IF(RC[-1]=0,1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.Delete
End With