Filtering daily data

Hello all,

I am stumped on the best way to handle this situation. I'll explain it
the best I can.

I have a macro that reads data from some log files. Every day at 12AM,
a new log file is created that will contain all of the data for that
day, from 12AM to 11:59:59PM.

This log file contains lines of data. Each line has a sequential
number. The numbering resets itself to 1 at the start of the
production day. This can be anywhere between 5AM and 6AM.

So, to get all of the numbers for one full production day, you have to
read data from two different log files. For instance, if you wanted
all of the data that relates to yesterday's production day, you would
need to start at yesterday's number 1 entry (which happened around 6AM
or so) and include the rest of the data after that entry, and you would
everything that happened before today's number 1 entry, not including
today's number one entry.

It is important to note that the times in which the numbers roll back
to number 1 to start the day are random.

Currently, I load the data file into Excel and do a filter based on
time. This is close, but doesn't always work, for the reasons stated
above.

What I really need is to find a way to say, "In yesterday's file, find
the #1 entry and delete everything that happened before it. Then, in
today's file, find the #1 entry and delete it, and everything that
happened after it."

I cannot seem to make this work. Can anyone help point me into the
right direction?

If you need more info, just ask.

Here is the data I have available:

Date / Time (military style) / Sequence number / Serial number
/ Product Type

Thanks!
Tim

Dim myCell As Range

'Open First File
Set myCell = Range("A:A").Find(1, , , xlWhole)
' Clear above that cell
Range("A1", myCell(0, 1)).EntireRow.Delete

'Open second file then
Set myCell = Range("A:A").Find(1, , , xlWhole)
'Clear that cell and below
Range(myCell, "E65536").EntireRow.Delete


HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Hello all,
>
> I am stumped on the best way to handle this situation. I'll explain it
> the best I can.
>
> I have a macro that reads data from some log files. Every day at 12AM,
> a new log file is created that will contain all of the data for that
> day, from 12AM to 11:59:59PM.
>
> This log file contains lines of data. Each line has a sequential
> number. The numbering resets itself to 1 at the start of the
> production day. This can be anywhere between 5AM and 6AM.
>
> So, to get all of the numbers for one full production day, you have to
> read data from two different log files. For instance, if you wanted
> all of the data that relates to yesterday's production day, you would
> need to start at yesterday's number 1 entry (which happened around 6AM
> or so) and include the rest of the data after that entry, and you would
> everything that happened before today's number 1 entry, not including
> today's number one entry.
>
> It is important to note that the times in which the numbers roll back
> to number 1 to start the day are random.
>
> Currently, I load the data file into Excel and do a filter based on
> time. This is close, but doesn't always work, for the reasons stated
> above.
>
> What I really need is to find a way to say, "In yesterday's file, find
> the #1 entry and delete everything that happened before it. Then, in
> today's file, find the #1 entry and delete it, and everything that
> happened after it."
>
> I cannot seem to make this work. Can anyone help point me into the
> right direction?
>
> If you need more info, just ask.
>
> Here is the data I have available:
>
> Date / Time (military style) / Sequence number / Serial number
> / Product Type
>
> Thanks!
> Tim
>

D'Oh!

Range(myCell, "E65536").EntireRow.Delete

Should have been

Range(myCell, "A65536").EntireRow.Delete

Not that it _really_ matters, but just for consistency....

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Dim myCell As Range
>
> 'Open First File
> Set myCell = Range("A:A").Find(1, , , xlWhole)
> ' Clear above that cell
> Range("A1", myCell(0, 1)).EntireRow.Delete
>
> 'Open second file then
> Set myCell = Range("A:A").Find(1, , , xlWhole)
> 'Clear that cell and below
> Range(myCell, "E65536").EntireRow.Delete
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hello all,
>>
>> I am stumped on the best way to handle this situation. I'll explain it
>> the best I can.
>>
>> I have a macro that reads data from some log files. Every day at 12AM,
>> a new log file is created that will contain all of the data for that
>> day, from 12AM to 11:59:59PM.
>>
>> This log file contains lines of data. Each line has a sequential
>> number. The numbering resets itself to 1 at the start of the
>> production day. This can be anywhere between 5AM and 6AM.
>>
>> So, to get all of the numbers for one full production day, you have to
>> read data from two different log files. For instance, if you wanted
>> all of the data that relates to yesterday's production day, you would
>> need to start at yesterday's number 1 entry (which happened around 6AM
>> or so) and include the rest of the data after that entry, and you would
>> everything that happened before today's number 1 entry, not including
>> today's number one entry.
>>
>> It is important to note that the times in which the numbers roll back
>> to number 1 to start the day are random.
>>
>> Currently, I load the data file into Excel and do a filter based on
>> time. This is close, but doesn't always work, for the reasons stated
>> above.
>>
>> What I really need is to find a way to say, "In yesterday's file, find
>> the #1 entry and delete everything that happened before it. Then, in
>> today's file, find the #1 entry and delete it, and everything that
>> happened after it."
>>
>> I cannot seem to make this work. Can anyone help point me into the
>> right direction?
>>
>> If you need more info, just ask.
>>
>> Here is the data I have available:
>>
>> Date / Time (military style) / Sequence number / Serial number
>> / Product Type
>>
>> Thanks!
>> Tim
>>
>
>

Bernie,

Thank you for the help! It seems to work great!

I did discover a problem though. It seems that there is an odd case
where the data didn't start at "1". Is there a way to find the lowest
number in the column and use that as the basis to delete either up or
down depending on the day?

TIm

Use this instead, in both cases:

Set myCell = Range("A:A").Find(Application.Min(Range("A:A")), , , xlWhole)

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Bernie,
>
> Thank you for the help! It seems to work great!
>
> I did discover a problem though. It seems that there is an odd case
> where the data didn't start at "1". Is there a way to find the lowest
> number in the column and use that as the basis to delete either up or
> down depending on the day?
>
> TIm
>

Bernie, this worked GREAT!

However,

Slight change of plans. I cannot delete the entire row, or else I will
impact other data on the sheet. So, I need to use this method of
finding the 1 in a range of columns, say A:G, and then delete the rows
in THOSE COLUMNS either above the 1, or after and including the 1,
based on the day.

I know this must be an easy adjustment, but I can not for the life of
me figure it out.

Thanks Berniw!
Tim

Deleting can affect a lot of things - how about we just clear the contents?

' Clear above that cell
Range("A1", myCell(0, 1)).Resize(, 7).ClearContents


'Clear that cell and below
Range(myCell, "E65536").)).Resize(,7).ClearContents


HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Bernie, this worked GREAT!
>
> However,
>
> Slight change of plans. I cannot delete the entire row, or else I will
> impact other data on the sheet. So, I need to use this method of
> finding the 1 in a range of columns, say A:G, and then delete the rows
> in THOSE COLUMNS either above the 1, or after and including the 1,
> based on the day.
>
> I know this must be an easy adjustment, but I can not for the life of
> me figure it out.
>
> Thanks Berniw!
> Tim
>

Ooops, this

Range(myCell, "E65536").)).Resize(,7).ClearContents

should have been

Range(myCell, "E65536").Resize(,7).ClearContents

Sloppy copy and paste. Sorry.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Deleting can affect a lot of things - how about we just clear the contents?
>
> ' Clear above that cell
> Range("A1", myCell(0, 1)).Resize(, 7).ClearContents
>
>
> 'Clear that cell and below
> Range(myCell, "E65536").)).Resize(,7).ClearContents
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Bernie, this worked GREAT!
>>
>> However,
>>
>> Slight change of plans. I cannot delete the entire row, or else I will
>> impact other data on the sheet. So, I need to use this method of
>> finding the 1 in a range of columns, say A:G, and then delete the rows
>> in THOSE COLUMNS either above the 1, or after and including the 1,
>> based on the day.
>>
>> I know this must be an easy adjustment, but I can not for the life of
>> me figure it out.
>>
>> Thanks Berniw!
>> Tim
>>
>
>

Bernie,

Thank you for all your help on this. I ended going with a delete,
where you delete the selected range and shift the cells up:

Set myCell = Range("A:H").Find(1, , , xlWhole)
Range(myCell, "A65536").Resize(, 8).Delete Shift:=xlUp

I have another problem that just manifested itself today.

The problem is that the logs are generated every day, whether there is
production or not. So, when I ran the report today for Friday and
Saturday, there was no "1" in Saturday's log to key off of. This
caused an error and the macro halted. The error is "Run-time error
'1004': Method 'Range' of object '_Global' failed. The line that it
wants me to debug is:

Range(myCell, "A65536").Resize(, 8).Delete Shift:=xlUp

I am assuming that I get the error because myCell has no value because
there wasn't a "1" to find in the previous line of code (see above
posts).

Is there a way to integrate some error handling for this?

Tim

Tim,

Did you try this variant, when there wasn't a 1 to be found?

Set myCell = Range("A:A").Find(Application.Min(Range("A:A")), , , xlWhole)

Otherwise, you need to check after the find:

'Find the 1
Set myCell = Range("A:H").Find(1, , , xlWhole)

'If a 1 is found, myCell is a range
'If it isn't found, it is Nothing
If Not myCell Is Nothing Then
Range(myCell, "A65536").Resize(, 8).Delete Shift:=xlUp
End If

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Bernie,
>
> Thank you for all your help on this. I ended going with a delete,
> where you delete the selected range and shift the cells up:
>
> Set myCell = Range("A:H").Find(1, , , xlWhole)
> Range(myCell, "A65536").Resize(, 8).Delete Shift:=xlUp
>
> I have another problem that just manifested itself today.
>
> The problem is that the logs are generated every day, whether there is
> production or not. So, when I ran the report today for Friday and
> Saturday, there was no "1" in Saturday's log to key off of. This
> caused an error and the macro halted. The error is "Run-time error
> '1004': Method 'Range' of object '_Global' failed. The line that it
> wants me to debug is:
>
> Range(myCell, "A65536").Resize(, 8).Delete Shift:=xlUp
>
> I am assuming that I get the error because myCell has no value because
> there wasn't a "1" to find in the previous line of code (see above
> posts).
>
> Is there a way to integrate some error handling for this?
>
> Tim
>