Deleting Rows using VBA macro

[TrueTears]

Hi everyone,

Please refer to the attached workbook.

So there are 3 columns, column A is dates, column B is values related to a bank and column C is "exitflag". Essentially what I want to do is the following:

If the number is 1 under "exitflag", then I want to delete the entire row, eg, in row 2, "exitflag" takes on a value of 1, so I want the entire row deleted such that both the date and the value related to the bank is also deleted. By delete I mean I want the row removed so that the next row "shifts" up so that no empty row appears. Also note that there is also the number 1 under column B, eg, row 201, I do not want to delete those, I only want the rows deleted where exitflag has a value of 1.

I have written the following code but for some reason I don't think it works (or if it does work, I have to keep running it over and over, it doesn't do what I want in one go):


Sub DeleteRows()

For Each R In Selection.Rows

For Each c In R.Cells(1, 3)
If c.Value = 1 Then
R.Delete
End If
Next

Next

End Sub

Also preferably I would like the code to be a "selection." code rather than specifying a range as I would like to reuse the code on other datasets but with different number of rows.

Much appreciated for any assistance!

[patel]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For j = LR To 2 Step -1
If Cells(j, 3) = 1 Then Rows(j).Delete
Next
End Sub

[TrueTears]

Thank you patel, works like a charm!