How to delete row if column is blank or certain text

[Steve D.]

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.

[Don Guillett]

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
[email protected]
"Steve D." <Steve [email protected]> wrote in message
news:[email protected]...
> 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.

[Steve D.]

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
> [email protected]
> "Steve D." <Steve [email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[Don Guillett]

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
[email protected]
"Steve D." <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> "Steve D." <Steve [email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

[Don Guillett]

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
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> "Steve D." <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>> [email protected]
>>> "Steve D." <Steve [email protected]> wrote in message
>>> news:[email protected]...
>>> > 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.
>>>
>>>
>>>
>
>

[Steve D.]

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
> [email protected]
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > [email protected]
> > "Steve D." <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>> [email protected]
> >>> "Steve D." <Steve [email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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.
> >>>
> >>>
> >>>
> >
> >
>
>
>