List Data Between Two Dates

[martellb]

Hi, I'm very new to Visual Basic.

I have an excel sheet containing a column of dates (C8:C17514) with each date being repeated 48 times (amongst other data).

I would like to create a script which by pressing commandbutton1 will list the all the dates including repeats between a selected start date and end date (chosen from combo boxes 1 and 2 respectively) in another column.

Thanks in advance

[MINCUS1308]

can you post a workbook?

[MINCUS1308]

Attached is my workbook: Book1.xlsm

Sub InterestingTest()
     FirstDate = CDate("1/1/1900")
     SencondDate = CDate("1/1/2020")
     MyOutputString = ""
     
     For i = 8 To 17514
          CurrentValue = Cells(i, "C").Value
          
          If Not IsDate(CurrentValue) Then GoTo NextIteration
          
          If (CurrentValue > FirstDate And CurrentValue < SencondDate) Or (CurrentValue < FirstDate And CurrentValue > SencondDate) Then
               MyOutputString = CurrentValue & vbNewLine & MyOutputString
          End If
          
NextIteration:
     Next i
     
     MsgBox MyOutputString
End Sub

I'm not sure if that's what you meant

You can point FirstDate and SecondDate to the respective ComboBoxes and it will spit out a list of every date listed between the given date (in the given set of data)
You can also call this Sub with the On_Click event in the form's module

[martellb]

That looks like that's almost right. I do however want the selection to be inclusive of the two chosen dates and i want it to output into a given column. I have attached an example worksheet for more clarity.

Book1.xlsm

[MINCUS1308]

For inclusion I will replace the < and > operands with <= and >=.
Not sure why you need the ActiveX controls for the drop downs - Using data validation is probably easier.
And shapes are easier to use as buttons in my opinion.

I made modifications to the file:
Book1.xlsm

Sub InterestingTest()
     FirstDate = CDate(Cells(3, "I").Value)
     SencondDate = CDate(Cells(4, "I").Value)
     
     If FirstDate = SencondDate Then MsgBox "same dates bro"
     
     Range("H8").Select
     Range(Selection, Selection.End(xlDown)).Select
     Range(Selection, Selection.End(xlToRight)).Select
     Selection.ClearContents
     
     j = 8
     
     For i = 8 To 17514
          CurrentValue = Cells(i, "B").Value
          
          If Not IsDate(CurrentValue) Then GoTo NextIteration
          
          If (CurrentValue >= FirstDate And CurrentValue <= SencondDate) Or (CurrentValue <= FirstDate And CurrentValue >= SencondDate) Then
               Cells(j, "H").Value = CDate(Cells(i, "B"))
               Cells(j, "I").Value = Cells(i, "C")
               Cells(j, "J").Value = Cells(i, "D")
               'if you are doing more than 3 or 4 of these there are better ways to do it
               'if your data set is less than 6000 lines this will be fine

               j = j + 1
          End If
          
NextIteration:
     Next i
End Sub

[martellb]

Thanks a lot man, that works perfectly but you're rightit is a bit slow when I added it to my larger data set. Any ideas ofperformance improvements?

haha I used ActiveX controls because I don't know what I’m doing but I populatedthem with a code that extracts only unique values from the set which was neededcos otherwise it would be filled with 48 copies of 365 dates.
Forcing myself to use VB for tasks that probably don’tneed it as a way of learning.

[MINCUS1308]

Using arrays is marginally faster if you are gathering, calculating, or moving large sets of data.
You can also suspend some application operations that will help. (Like Application.ScreenUpdating = False)

I'm glad I was able to help you with your problem.
If its all wrapped up will you mark this thread as solved?
[Top right corner of the thread>Thread Tools>Mark as Solved]

Welcome to the Forum!

[p45cal]

Try the attached to see if it's faster. (Is it the combo population which is slow or the rest?)

[martellb]

Try the attached to see if it's faster. (Is it the combo population which is slow or the rest?)

Thank you, that is a lot faster but I does have the downside of requiring those extra 6 cells.

Loading the comboboxes wasn't an issue but they don't automatically laoad when the sheet is opened.

[p45cal]

Thank you, that is a lot faster but I does have the downside of requiring those extra 6 cells.

Those cells can be anywhere, even on a hidden sheet.

Loading the comboboxes wasn't an issue but they don't automatically laoad when the sheet is opened.

Well, you can choose to call that sub on sheet activation, or on workbook opening or when any value in the date column is changed. It depends on how often the comboboxes are likely to need updating. If the dates are put there by macro code, why not call that sub at the end of that code?