For Next Loop Doubt - Deleting Rows

[Maria Clara]

Hello All again,


I'm having trouble in trying to write some for next loops. I have an spreadsheet with 9 columns and on the 8th column I have some rows that are with value 0 which I need to remove the entire Row.


When I run the Macro below it does not remove all rows and I have to run it twice to do the job.


Could you pls check my code? Thank you!


File attached: http://www.sendspace.com/file/h44kdp

**In this file for eg. I have 10 rows with 0 value, I run the macro and It removes only 9 rows. After that I need to run it again to remove the last row.


Dim i As Integer

Dim LastRow As Long


Range("A1").Select


LastRow = Worksheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Row - 1

Worksheets("Plan1").Activate


For i = 1 To LastRow

If Cells(i + 1, 8).Value = 0 Then Cells(i + 1, 8).EntireRow.Delete

Next i


End Sub

 

[NARAYANK991]

Hi Maria ,


I have not downloaded your file as yet ; just going through the code you have posted , I can see one problem ; whenever you delete a row , the remaining rows are moved up ; as a result , you could be deleting unintended rows.


When you delete using VBA , start deleting from the bottom of a range and move upwards , so that the indexing is done correctly.


Try this :

[pre]

Sub Delete_Rows()
Dim i As Integer
Dim LastRow As Long

Worksheets("Plan1").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row - 1

For i = LastRow To 1 Step -1
If Cells(i + 1, 8).Value = 0 Then Cells(i + 1, 8).EntireRow.Delete
Next i
End Sub

[/pre]
There is no problem in the code except this ; the code is deleting the correct number of rows , but not the right ones.


In some unused column , enter serial numbers starting from 2 through whatever is the lastrow , and check these numbers after running the code , to see which rows , and how many rows have been deleted.


Suppose the last row which has data in column A is 37 ; your lastrow will have the value of 36 , since you are subtracting 1 from 37.


Now , when you go from row 2 ( since you are starting the delete from row i+1 where i starts from 1 ) , to row 37 ( you end the delete when i = 36 , and row number i+1 is deleted ) , you are deleting 36 rows.


Is this what you want to do ? Of course , I have assumed that all the rows have 0 in column H ( column #8 ).


Narayan

 

[shrivallabha]

Hi Maria,


You can skip loop option altogether using Autofilter.

[pre]

Option Explicit
Public Sub DeleteRows()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next
With Range("A1:H" & LastRow)
.AutoFilter 8, 0
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter 8
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Maria Clara]

Narayan, thank you for your explanation and help, it has worked perfectly. I'm still trying to get used with For Next Loop since I heard it is good for a lot of things so your explanation was very valuable.


Shrivallabha, thank you so much for your help also, always interesting learning new ways to do stuff.


Regards.