Prev: Saving as Binary
Next: Symbols used in excel
From: Poohberry on 20 May 2010 22:22
I have a spreadsheet that I want to delete the entire row if it contains
04-29-2010 but there is a time stamp that changes after that.

How do I delete all the rows containing 04-29-2010 with random data after?

Thanks in advance for your brilliance!
From: ozgrid.com on 20 May 2010 23:12
Assumes valid dates & times are in Column A with Range A1 being a heading;

Sub DoIt()
Dim lRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
On Error Resume Next
For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 2
If Int(Cells(lRow, "A")) _
= DateSerial(2010, 4, 29) Then _
Cells(lRow, "A").EntireRow.Delete
Next lRow
On Error GoTo 0
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Poohberry" <Poohberry(a)discussions.microsoft.com> wrote in message
news:62D5969A-06A5-4438-AF35-75CAC3D9C6D8(a)microsoft.com...
>I have a spreadsheet that I want to delete the entire row if it contains
> 04-29-2010 but there is a time stamp that changes after that.
>
> How do I delete all the rows containing 04-29-2010 with random data after?
>
> Thanks in advance for your brilliance!

From: Poohberry on 20 May 2010 23:59
04-29-2010 10:53:03 AM
39 / 1417

that is an example it was a txt file now a .csv opened in excel 07 all on
column a

"ozgrid.com" wrote:

> Assumes valid dates & times are in Column A with Range A1 being a heading;
>
> Sub DoIt()
> Dim lRow As Long
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> .Calculation = xlCalculationManual
> End With
> On Error Resume Next
> For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 2
> If Int(Cells(lRow, "A")) _
> = DateSerial(2010, 4, 29) Then _
> Cells(lRow, "A").EntireRow.Delete
> Next lRow
> On Error GoTo 0
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> .Calculation = xlCalculationAutomatic
> End With
> End Sub
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Poohberry" <Poohberry(a)discussions.microsoft.com> wrote in message
> news:62D5969A-06A5-4438-AF35-75CAC3D9C6D8(a)microsoft.com...
> >I have a spreadsheet that I want to delete the entire row if it contains
> > 04-29-2010 but there is a time stamp that changes after that.
> >
> > How do I delete all the rows containing 04-29-2010 with random data after?
> >
> > Thanks in advance for your brilliance!
>
> .
>
From: Pritesh on 21 May 2010 01:28
Step1: For time-being, you can change cell formatting such way that it does
not show time but only shows date in mm-dd-yyyy format.

Step2: Apply data filter

Step3: From filter drop-down, select/filter to show dates that you want to
delete.

Step4: Select all visible/filtered lines and say Delete, it will ask if you
want to delete entire row?, say yes.
--
Regards,
Pritesh


"Poohberry" wrote:

> I have a spreadsheet that I want to delete the entire row if it contains
> 04-29-2010 but there is a time stamp that changes after that.
>
> How do I delete all the rows containing 04-29-2010 with random data after?
>
> Thanks in advance for your brilliance!
From: Poohberry on 21 May 2010 11:00
I actualy need to remove the data filtering it will confuse people, I need it
to just be gone.

"Pritesh" wrote:

> Step1: For time-being, you can change cell formatting such way that it does
> not show time but only shows date in mm-dd-yyyy format.
>
> Step2: Apply data filter
>
> Step3: From filter drop-down, select/filter to show dates that you want to
> delete.
>
> Step4: Select all visible/filtered lines and say Delete, it will ask if you
> want to delete entire row?, say yes.
> --
> Regards,
> Pritesh
>
>
> "Poohberry" wrote:
>
> > I have a spreadsheet that I want to delete the entire row if it contains
> > 04-29-2010 but there is a time stamp that changes after that.
> >
> > How do I delete all the rows containing 04-29-2010 with random data after?
> >
> > Thanks in advance for your brilliance!
 | 
Pages: 1
Prev: Saving as Binary
Next: Symbols used in excel