deleting the rows depending on the result of a condition involving two columns.

[pkseelam]

Hi,

I need to either delete or keep the rows in the sheet depending on the
result of the condition involving two columns. One column is a test
date with this "02/03/2000" format. I need to extract the year part
from this cell and then add this year (in this case, 2000) to another
column which has whole integers (e.g. "3"). So, the result is 2003
which being the current year; if the result is equal to the current
year then, I want to keep that row. On the otherhand, if the result is
lesser or greater than the current year, I want to delete that
particular ROW completely. So, at the end of this operation, the sheet
must have all the rows in it such that, the sum of the year from the
date column and the 'whole integer' column must be equal to the current
year. I have to do this for a workbook which has many sheets. I need
help with this very soon. Thank you very much in advance.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Tom Ogilvy]

Assume dates are in Column D starting in row 2

Assume the integer is in Column I (offset(0,5) from D) and each year in D
is added to the corresponding integer in the same row in column I

Sub DeleteBasedOnYear()
Dim rng as Range
Dim cell as Range
Dim rng1 as Range
set rng = Range(cells(2,"D"),cells(rows.count,"D").End(xlup))
for each cell in rng
if isdate(cell.value) then
if year(cell.value) + cell.offset(0,5) = year(date) then
' do nothing
else
if rng1 is nothing then
set rng1 = cell
else
set rng1 = Union(rng1,cell)
end if
end if
end if
Next
if not rng1 is nothing then
rng1.Entirerow.Delete
end if
End Sub

 

[pkseelam]

thank you for the help Tom. I was getting the error because I didnt
Unprotect the sheet. Thank you once again.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------