TrueTears
08-04-2013, 12:08 AM
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!
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!