Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting the rows depending on the result of a condition involving two columns.
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. ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting the rows depending on the result of a condition involving two columns.
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 -- Regards, Tom Ogilvy "pkseelam" wrote in message ... 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. ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting the rows depending on the result of a condition involving two columns.
thank you for the help Tom. I was getting the error because I didnt
Unprotect the sheet.;) Thank you once again. ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows or columns | Excel Worksheet Functions | |||
deleting rows in a worksheet if condition is met | Excel Discussion (Misc queries) | |||
Deleting Rows and columns | Excel Discussion (Misc queries) | |||
condition formatting involving formula | Excel Worksheet Functions | |||
Deleting Rows and columns | Excel Discussion (Misc queries) |