Macro to find user input date range and delete everything outside range

Hi Guys,

Wondering if someone could help me in developing a simple user interfacing macro. I have a column with dates of a specified range shown below:

dateRange.PNG

The date range can be an arbitrary range so could go from any start date to end date (most of the time its a 6/8 weeks). Lets say for arguments sake the date range goes from 31/12/2014 18:00 to 09/02/2015 18:00 (note date is UK format of dd/mm/yyyy). I would like the user to be asked for a start and end date range that they went - say 01/01/2015 to 31/01/2015. Once they have chosen the range the macro should delete everything BEFORE their selected date range (and shift cells up) and delete everything AFTER their selected date range. The date range is in increments of 10 minutes.

I'm pretty new to the whole User input through InputBox concepts so any advice would be much appreciated! Especially what you do with these input values if you want to use them outside the Sub routine.

Also, it would be EPIC if some fail/error handling was in there - so the macro would revert back to asking them to input the date range again IF
- if they put a date range that is outside the original date range
- if they put in a wrong date format

I'm not asking for a full blown macro, just somewhere to start would be great! Let me know if you need the workbook uploaded.

Cheers.

Here's something to get you started.

Please Login or Register  to view this content.

Especially what you do with these input values if you want to use them outside the Sub routine.

You can use Functions to pass values outside the sub routine. A very simple example follows. If you want the input values to be "remembered" the next time the sub is run, you'll need to save them somewhere in the workbook.

Please Login or Register  to view this content.

Set is only used when setting a variable equal to and object. You wouldn't use Set before sRow because your use of .Row makes sRow just an integer, not an object. You WOULD use Set if you were setting sRow equal to the Range, which is an object.

Anyway, the error happens because you haven't defined what lastRow is in this sub. If you're saying that you did define it in another sub, then pass it into this sub. As it is now, you're passing in sDate and eDate; there's no reason to do that, because you immediately set their values with the input boxes.

I dimensioned lastRow as a GLOBAL variable at the start so it was available to this sub routine. As can be seen from the screenshot below, the variable lastRow does indeed have a value:

lastRow.PNG

Hmm, I can't replicate the error. Would need to see the workbook, or an example workbook where the error also happens.

I still can't replicate the error. The line you say you get an error runs fine for me. See screenshot below, both sRow and eRow have a value.

NoError.png

Now im confused...im still getting that error at sRow.

I did notice that your end date format is "2/9/2015 5", while in mine it is "09/02/2015"...they are both 10 characters but yours removes the 0 from the day and month and switched the date format to US style. Could this have some kind of impact? All my dates (both data values in sheets & user inputs) are in UK date format: DD/MM/YYYY

Could be. Try forcing formatting on startDate and endDate like this:

Please Login or Register  to view this content.

No good...wow this is annoying.

I forced the format for startDate/endDate & sDate/eDate. The first two dates are just to show the user what the original start and end dates are. The second two dates are the user input dates.

Getting the same error.

Fixed it!! I used the CDate function in VBA to convert the userinput variable to a date. Thus the FIND function was able to work properly as it was looking for a date within a range of dates. So, format was the issue.

Thanks a lot for your help and advice!!

Great! Dates can be finicky, I know.

Please mark this thread as [SOLVED], and any reputation (the * in the lower left of my posts) would be appreciated.