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

Filtering daily data
 

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


wrote in message
oups.com...
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

Filtering daily data
 

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
...
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


wrote in message
oups.com...
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

Filtering daily data
 

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

Filtering daily data
 

Use this instead, in both cases:

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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

Filtering daily data
 

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

Filtering daily data
 

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


wrote in message
oups.com...
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

Filtering daily data
 

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
...
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


wrote in message
oups.com...
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

Filtering daily data
 

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

Filtering daily data
 

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


wrote in message
oups.com...
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