in a table with date and time, how to automatically delete first rows when time passed?

**netanel99** · 06-17-2014, 06:28 PM

hello excel gurus !
with much respect for you guys , I would like to ask you if you could help me to automatically delete the first row in a table when the date and time columns are passed the current date.
I have a table with date column and time column, it's chronically arranged so the coming dates will be firsts and the distant dates will be last, and what I'm trying to do is that every date and hour that passed will be automatically deleted.(meaning all the row'll be deleted so there won't be any "passed" items)

it will be amazing if not only the first row will be deleted , but it will also show a popup message that displaying the 2nd column of the row - which contain the assignment item itself (it's description).

that'll really awesome and I can't put it on words how grateful I'm for your highly skilled help, and bright solutions very appreciated - thanks for any help.

btw- the reason that I'm trying to have this , is because I really strive to have a system that could display the first 10 signal items (it's like todo's but not exactly because it's dynamically changing all the time), so I could act upon them and those would pushed automatically upward based on the time.
I've added the excel file - it demonstrate visually which better than my words.

netanel.
hello.xlsx

**lexusap** · 06-18-2014, 01:03 AM

Hi Netanel99,

Please see attached File i created. I have set the macro to run every 5 seconds. you can adjust it to your liking.
So i only sort it by date and if that line has passed, data will be deleted.

Cheers
Lex

**netanel99** · 06-18-2014, 10:29 AM

hello lex , really appreciate the help,
things start to look awesome , but even so I still face some major challenges, hope you could help with some issues,
I've updated few things in my original file , the challenges are:
*every 5 second the macro leads the user to the "Auto Schedule" sheet. if that just calculate in in it's "auto schedule" sheet alone in the background and user could navigate in other sheets without being notice of that- it'll be greattt.
*if the message will be dynamically the column B of the table when the title of the column is "this is the message" that will be great because that is the description of the item - much much beneficial.

thanks so far, and for any further help ^_^

**netanel99** · 06-19-2014, 05:47 PM

trying to ask again for help with it ?

**lexusap** · 06-19-2014, 11:33 PM

Hi Netanel,

bump up again. Was not infront of my computer yesterday. going to resolve this now.

Cheers
Lex

**lexusap** · 06-19-2014, 11:46 PM

Hi Netanel,

see attached updated spreadsheet. i have turn of the screen updating and in the message pop up box will show the description of column B.

Thanks
Lex

Dont forget to Star me/Add reputation on the bottom left if you are happy with the result!

**netanel99** · 06-20-2014, 12:22 AM

wow lex ! that's alllmost perfect

thanks for that - BiG reputation for you, plus my sister , just pls help me with the last issue with it

hopefully you could modify the code so it'll work even so user seeing other sheets in the worksheet even if I'll create new sheet it still work without it directing the user to the "auto schedule" sheet .
the only problem with a possible solution is that i'll paste this code on some heavy duty files - if that recalculate all the worksheet I'll get frozen file, I guess that if the recalculation will refer only to the "auto schedule" sheet and without directing to the sheet itself - it will be SWEET.

Lex I can't thank you enough!
netanel.

**lexusap** · 06-20-2014, 12:40 AM

Hi,

With this amendment, you can browse and open multiple workbook and let the macro run on the background!

Cheers
Lex

**netanel99** · 06-20-2014, 02:40 AM

thanks for the follow up , really appreciate it !!!
It works just great, but that's weird I can't attach this macro to another worksheet with it to work, the problem line is:
" Workbooks(A).Sheets("auto schedule").Range("A5:P" & EOR).Sort key1:=Workbooks(A).Sheets("auto schedule").Range("D5").Value, order1:=xlAscending, Header:=xlYes"

of course I have a tab named "auto schedule" and put the table in the exact location in the "auto schedule" sheet.
I'm sorry I push it man you already helped me, I just plan to use it a lot everyday ,
also If I open the file after few days I have tens of todo's keep deleted one by one.. I didn't thought about it sadly.

thanks for the help so far ! ! !

in a table with date and time, how to automatically delete first rows when time passed?

LinkBack

Thread Tools

Rate This Thread

Display

in a table with date and time, how to automatically delete first rows when time passed?

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Re: in a table with date and time, how to automatically delete first rows when time passed

Thread Information

Users Browsing this Thread

Similar Threads

I need to lock cells after date has passed on time shees.

[SOLVED] Pop Up Box When Date/Time has passed (-1Days 7hrs 31mins)

find date&time then delete rows after that.

highlighting date when passed certain time

Date/time passed formula

Bookmarks

Bookmarks

Posting Permissions