Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete row if column is blank or certain text
Hi all, sorry for asking this question, but I am new and still learning VBA,
but would like a little help if possible. What I would like to have happen, is if a cell in column C is blank, or contains a set of numbers like '1540/06', then it would delete that row. The last two numbers is the day of the month, so if the last two numbers were not the current day of month, then it would delete that row. Example below; lets say todays date is the 5th, I would like it to delete rows 1 & 3, because the date is incorrect in Col C in row 1, and blank cell in Col C in row 3. A B C 363 MEM 1450/06 616 BOS 2356/05 225 MEM 455 LAX 1767/05 224 LAX 0540/05 Thanks for any help you could give. Steve D. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete row if column is blank or certain text
Thanks for the quick reply Don. I am still having a problem, it won't delete
all the rows if Column C is blank,(it does some), and for seeing the last two numbers after the '/' as a date, and deleting the row if those numbers are not of todays date, it deletes some of them, but not all. Thanks....Steve D. "Don Guillett" wrote: try Sub deleteifnottoday() For Each c In Range("c12:c18") If Len(c) 0 And Right(c, 2) < _ Format(Day(Date), "00") Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software "Steve D." <Steve wrote in message ... Hi all, sorry for asking this question, but I am new and still learning VBA, but would like a little help if possible. What I would like to have happen, is if a cell in column C is blank, or contains a set of numbers like '1540/06', then it would delete that row. The last two numbers is the day of the month, so if the last two numbers were not the current day of month, then it would delete that row. Example below; lets say todays date is the 5th, I would like it to delete rows 1 & 3, because the date is incorrect in Col C in row 1, and blank cell in Col C in row 3. A B C 363 MEM 1450/06 616 BOS 2356/05 225 MEM 455 LAX 1767/05 224 LAX 0540/05 Thanks for any help you could give. Steve D. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete row if column is blank or certain text
the len(c)0 is meant to KEEP blanks because you said you ONLY wanted to
delete those without todays date. Perhaps you can send a SMALL workbook to my private email below. -- Don Guillett SalesAid Software "Steve D." wrote in message ... Thanks for the quick reply Don. I am still having a problem, it won't delete all the rows if Column C is blank,(it does some), and for seeing the last two numbers after the '/' as a date, and deleting the row if those numbers are not of todays date, it deletes some of them, but not all. Thanks....Steve D. "Don Guillett" wrote: try Sub deleteifnottoday() For Each c In Range("c12:c18") If Len(c) 0 And Right(c, 2) < _ Format(Day(Date), "00") Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software "Steve D." <Steve wrote in message ... Hi all, sorry for asking this question, but I am new and still learning VBA, but would like a little help if possible. What I would like to have happen, is if a cell in column C is blank, or contains a set of numbers like '1540/06', then it would delete that row. The last two numbers is the day of the month, so if the last two numbers were not the current day of month, then it would delete that row. Example below; lets say todays date is the 5th, I would like it to delete rows 1 & 3, because the date is incorrect in Col C in row 1, and blank cell in Col C in row 3. A B C 363 MEM 1450/06 616 BOS 2356/05 225 MEM 455 LAX 1767/05 224 LAX 0540/05 Thanks for any help you could give. Steve D. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete row if column is blank or certain text
After seeing the workbook, this is the solution. Dates need to be trimmed
and you need to go from the bottom up. Sub deleteifnottoday() x = Cells(Rows.Count, "a").End(xlUp).Row For i = x To 8 Step -1 If Right(Trim(Cells(i, "c")), 2) < Format(Day(Date), "00") _ Then Cells(i, "c").EntireRow.Delete Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... the len(c)0 is meant to KEEP blanks because you said you ONLY wanted to delete those without todays date. Perhaps you can send a SMALL workbook to my private email below. -- Don Guillett SalesAid Software "Steve D." wrote in message ... Thanks for the quick reply Don. I am still having a problem, it won't delete all the rows if Column C is blank,(it does some), and for seeing the last two numbers after the '/' as a date, and deleting the row if those numbers are not of todays date, it deletes some of them, but not all. Thanks....Steve D. "Don Guillett" wrote: try Sub deleteifnottoday() For Each c In Range("c12:c18") If Len(c) 0 And Right(c, 2) < _ Format(Day(Date), "00") Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software "Steve D." <Steve wrote in message ... Hi all, sorry for asking this question, but I am new and still learning VBA, but would like a little help if possible. What I would like to have happen, is if a cell in column C is blank, or contains a set of numbers like '1540/06', then it would delete that row. The last two numbers is the day of the month, so if the last two numbers were not the current day of month, then it would delete that row. Example below; lets say todays date is the 5th, I would like it to delete rows 1 & 3, because the date is incorrect in Col C in row 1, and blank cell in Col C in row 3. A B C 363 MEM 1450/06 616 BOS 2356/05 225 MEM 455 LAX 1767/05 224 LAX 0540/05 Thanks for any help you could give. Steve D. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete row if column is blank or certain text
Don,
Thanks for all the help, it works great. Thanks, Steve "Don Guillett" wrote: After seeing the workbook, this is the solution. Dates need to be trimmed and you need to go from the bottom up. Sub deleteifnottoday() x = Cells(Rows.Count, "a").End(xlUp).Row For i = x To 8 Step -1 If Right(Trim(Cells(i, "c")), 2) < Format(Day(Date), "00") _ Then Cells(i, "c").EntireRow.Delete Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... the len(c)0 is meant to KEEP blanks because you said you ONLY wanted to delete those without todays date. Perhaps you can send a SMALL workbook to my private email below. -- Don Guillett SalesAid Software "Steve D." wrote in message ... Thanks for the quick reply Don. I am still having a problem, it won't delete all the rows if Column C is blank,(it does some), and for seeing the last two numbers after the '/' as a date, and deleting the row if those numbers are not of todays date, it deletes some of them, but not all. Thanks....Steve D. "Don Guillett" wrote: try Sub deleteifnottoday() For Each c In Range("c12:c18") If Len(c) 0 And Right(c, 2) < _ Format(Day(Date), "00") Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software "Steve D." <Steve wrote in message ... Hi all, sorry for asking this question, but I am new and still learning VBA, but would like a little help if possible. What I would like to have happen, is if a cell in column C is blank, or contains a set of numbers like '1540/06', then it would delete that row. The last two numbers is the day of the month, so if the last two numbers were not the current day of month, then it would delete that row. Example below; lets say todays date is the 5th, I would like it to delete rows 1 & 3, because the date is incorrect in Col C in row 1, and blank cell in Col C in row 3. A B C 363 MEM 1450/06 616 BOS 2356/05 225 MEM 455 LAX 1767/05 224 LAX 0540/05 Thanks for any help you could give. Steve D. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to delete rows if cell blank in column | Excel Discussion (Misc queries) | |||
Delete row if blank in a column | Excel Worksheet Functions | |||
I cannot delete a blank column from an Excel spreadsheet. | Excel Discussion (Misc queries) | |||
If field in column is blank, delete if.... | Excel Programming | |||
Delete Entire Row If Column C is Blank | Excel Programming |