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


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

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

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


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

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


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

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