I have a gi-normous list of info that gets imported from an outside database.

One of the columns - in the middle of the dang thing - is the "Event Date".

Sometimes I want to look at all the information related to events on a
specific day.

So what I'd like to do is:
- Create a cell - let's call it $A$2 - that has my "Desired Date" in it.
- Run a macro that deletes all rows that are NOT the date specified in cell
$A$2.

I have seen macros that use "ActiveCell" to accomplish this... But I want
for it literally to navigate to the right column on its own and do this.

In other words, I want the user to - no matter which cell they're in - click
on the macro button, and have Excel:
- Go to the first "Date" field (which on the first iteration will be $G4).
- Compare it to the "Desired Date" ($A$2).
- If it doesn't match, delete the whole row, and go to the next.
- If it DOES match, go down one row and do the comparison again.

There will be multiple entries for one date, so it will need to stop, think,
make a decision, and then go on.

There are also multiple dates in each row, so I can't just do a "For Each"
either.

(And also I will need the loop to stop when it runs out of rows... So how
would I tell it to "Do Until" column G is done?)

Thoughts?

(OK, more specific, "SIMPLE Thoughts?")

Thank you,

rjb

Sub DeleteRows()
Dim iEnd As Integer
Dim iCt As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iEnd = ws.Cells(65536, 7).End(xlUp).Row
For iCt = iEnd To 4 Step -1
If ws.Cells(iCt, 7) < ws.Cells(2, 1) Then
ws.Rows(iCt).Delete
End If
Next iCt
End Sub

Hth,
Merjet

You want simple?????

Drop the idea of deleting your data--you may actually find that you'll need it
later anyway.

But you can select the range with dates (G3:Gxxxx, include the header)
then do data|filter|autofilter
filter to show the today's date--or any other date you need.

I don't understand how other dates in the row affect your process, though.

RJB wrote:

I have a gi-normous list of info that gets imported from an outside database.

One of the columns - in the middle of the dang thing - is the "Event Date".

Sometimes I want to look at all the information related to events on a
specific day.

So what I'd like to do is:
- Create a cell - let's call it $A$2 - that has my "Desired Date" in it.
- Run a macro that deletes all rows that are NOT the date specified in cell
$A$2.

I have seen macros that use "ActiveCell" to accomplish this... But I want
for it literally to navigate to the right column on its own and do this.

In other words, I want the user to - no matter which cell they're in - click
on the macro button, and have Excel:
- Go to the first "Date" field (which on the first iteration will be $G4).
- Compare it to the "Desired Date" ($A$2).
- If it doesn't match, delete the whole row, and go to the next.
- If it DOES match, go down one row and do the comparison again.

There will be multiple entries for one date, so it will need to stop, think,
make a decision, and then go on.

There are also multiple dates in each row, so I can't just do a "For Each"
either.

(And also I will need the loop to stop when it runs out of rows... So how
would I tell it to "Do Until" column G is done?)

Thoughts?

(OK, more specific, "SIMPLE Thoughts?")

Thank you,

rjb

--

Dave Peterson

I was curious why you would want to delete the data also. Might you not want
to enter a different date to check that data?

A pivot table would work real well in this situation. You would be able to
choose the date from a list of entered dates and it would show you all the
data instantly. Choose a different date and get the data. Even multiple
dates can be chosen to see data for a period of time. Even manually, it
takes seconds to set up a pivot table.

Regards,

Alan


"RJB" wrote in message
...
I have a gi-normous list of info that gets imported from an outside
database.

One of the columns - in the middle of the dang thing - is the "Event
Date".

Sometimes I want to look at all the information related to events on a
specific day.

So what I'd like to do is:
- Create a cell - let's call it $A$2 - that has my "Desired Date" in it.
- Run a macro that deletes all rows that are NOT the date specified in
cell
$A$2.

I have seen macros that use "ActiveCell" to accomplish this... But I want
for it literally to navigate to the right column on its own and do this.

In other words, I want the user to - no matter which cell they're in -
click
on the macro button, and have Excel:
- Go to the first "Date" field (which on the first iteration will be $G4).
- Compare it to the "Desired Date" ($A$2).
- If it doesn't match, delete the whole row, and go to the next.
- If it DOES match, go down one row and do the comparison again.

There will be multiple entries for one date, so it will need to stop,
think,
make a decision, and then go on.

There are also multiple dates in each row, so I can't just do a "For Each"
either.

(And also I will need the loop to stop when it runs out of rows... So how
would I tell it to "Do Until" column G is done?)

Thoughts?

(OK, more specific, "SIMPLE Thoughts?")

Thank you,

rjb

OK, I have absolutely no idea what this means. It looks like you gave me a
macro to compare the seventh column of each row with the cell two above it,
and delete if it doesn't match?

"merjet" wrote:

Sub DeleteRows()
Dim iEnd As Integer
Dim iCt As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iEnd = ws.Cells(65536, 7).End(xlUp).Row
For iCt = iEnd To 4 Step -1
If ws.Cells(iCt, 7) < ws.Cells(2, 1) Then
ws.Rows(iCt).Delete
End If
Next iCt
End Sub

Hth,
Merjet

OK, I have absolutely no idea what this means. It looks like you gave me a
macro to compare the seventh column of each row with the cell two above it,
and delete if it doesn't match?

Not quite. It compares each row in the 7th (G) column to cell A2 and
deletes the row if they don't match. That is what you asked for.

Merjet

Wow... I typed a long response and lost it; let's see if I can give a short
one.

The data that comes out of our manufacturing system is huge and robust, but
for the purpose of this report, I only need a few variables on certain lines.
By DELETING what I don't need, I can save the spreadsheet and email, etc. If
I save ALL of the data, to be recut later, I can't email it - the spreadsheet
is just too big and cumbersome.

It takes no time to import a huge field of stuff from the MS to Excel, and
it will take weeks to get corporate to reprogram a query to only pull what I
need (and only after I prove to their satisfaction why I want the data THAT
way instead of THIS way!), so it is actually much more efficient to import
EVERYTHING, and strip mine out what I don't want.

The reason why it needs to have variables for dates is, sometimes my
managers will want to compare different things.

One of my managers may be interested in how we performed on Mondays in one
department for one quarter vs. another; another manager may be interested in
each week's performance as opposed to the same week last year. Some of their
querying may prove valuable, some may not; I'd rather give them the ability
to quickly cut it and see what helps them manage than tell them what I think
they should be looking at. Who knows, one of them may stumble upon the next
great management fad.

The reason why Pivot Tables don't work - from what I can tell of PivotTables
- is that I am looking at more than three variables, across two discreet
periods.

In other words, the variables I am looking at:
MACHINE STOP DATE START DATE DOWN TIME LOST REVENUE LOST
LABOUR

Now, "Lost Revenue" and "Lost Labour" are functions of "Down Time", but not
straight-line. I can have a machine running expensive product one day, and
cheap product the next; it's useful to know both how much time my machine
isn't running, and how much money I'm not making off the machine.

The way I understand Pivot Tables, I can get a pretty nice graph of any two
sets of variables against one constant, but not say, all three variables
against one constant.

(And remember, I want to compare these vs. historical averages I've already
got in Excel.)

Anyway, I will try merjet's fix, and see if that does the trick. I sure
don't understand it, but it doesn't matter if it works, right!

Thanks everyone.

rjb

"Alan" wrote:

I was curious why you would want to delete the data also. Might you not want
to enter a different date to check that data?

A pivot table would work real well in this situation. You would be able to
choose the date from a list of entered dates and it would show you all the
data instantly. Choose a different date and get the data. Even multiple
dates can be chosen to see data for a period of time. Even manually, it
takes seconds to set up a pivot table.

Regards,

Alan


"RJB" wrote in message
...
I have a gi-normous list of info that gets imported from an outside
database.

One of the columns - in the middle of the dang thing - is the "Event
Date".

Sometimes I want to look at all the information related to events on a
specific day.

So what I'd like to do is:
- Create a cell - let's call it $A$2 - that has my "Desired Date" in it.
- Run a macro that deletes all rows that are NOT the date specified in
cell
$A$2.

I have seen macros that use "ActiveCell" to accomplish this... But I want
for it literally to navigate to the right column on its own and do this.

In other words, I want the user to - no matter which cell they're in -
click
on the macro button, and have Excel:
- Go to the first "Date" field (which on the first iteration will be $G4).
- Compare it to the "Desired Date" ($A$2).
- If it doesn't match, delete the whole row, and go to the next.
- If it DOES match, go down one row and do the comparison again.

There will be multiple entries for one date, so it will need to stop,
think,
make a decision, and then go on.

There are also multiple dates in each row, so I can't just do a "For Each"
either.

(And also I will need the loop to stop when it runs out of rows... So how
would I tell it to "Do Until" column G is done?)

Thoughts?

(OK, more specific, "SIMPLE Thoughts?")

Thank you,

rjb