none
Excel AutoFilters in VBA Using Dates RRS feed

  • Question

  • How to format my datavalue to be independet of the Windows regional setting for date-format?

    I have a table in Excel worksheet with one column (Column B) containing date range as follows (dd-mm-yyyy hh:mm:ss). I want to use AutoFilter to filter data from the rest of the table between two dates from column B. Autofilter only works with US regional settings selected from the Windows control panel.... How should I format my code to run on for instance Danish regional settings?

    This is the peace of my code which does not work as intended (it works only with US regional settings)

    Sub selectrange()
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim starttime As Date
        Dim starttimestr As String
        Dim endtime As Date
        Dim endtimestr As String
        Dim selectedCells As Range
        Dim stime As String
        Dim etime As String

        stime = InputBox ("start time")
        etime = InputBox ("end time")

        With Sheets("Sheet1")
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If

            .Columns("B:B").NumberFormat = "dd-mm-yyyy hh:mm"
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            starttime = DateValue(stime) + TimeValue(stime)
            starttimestr = Format(starttime, "dd-mm-yyyy hh:mm")
            endtime = DateValue(etime) + TimeValue(etime)
            endtimestr = Format(endtime, "dd-mm-yyyy hh:mm")

            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr

             Set selectedCells = .Range(.Range("A1"), .Cells(LastRow, LastCol)).SpecialCells(xlCellTypeVisible)
        End With
        With Sheets("sheet2")
           selectedCells.Copy _
              Destination:=.Range("A1")
        End With
    End Sub

    Any suggestions or proposals?

    I have heard about DateSerial Function, but I am quite green at VBA programming...

    Thank you for assistance!

                                       
    • Moved by Youjun Tang Tuesday, December 16, 2014 8:40 AM more related to suggestion forum
    Monday, December 15, 2014 10:01 PM

Answers

  • Hi GyTasS,

    Base on my test in Excel 2013, it seems the issue in excel. I tried record macro and run that macro, it even doesn’t work (for Danish)!

    To deal with that issue, I change the format (for input date) to “MM/dd/yyyy hh:mm” and it works fine. Please try it and check whether it works.

    Sub selectrange()
    
         Dim LastRow As Integer
    
         Dim LastCol As Integer
    
         Dim starttime As Date
    
         Dim starttimestr As String
    
         Dim endtime As Date
    
         Dim endtimestr As String
    
         Dim selectedCells As Range
    
         Dim stime As String
    
         Dim etime As String
    
         stime = InputBox("start time")
    
         etime = InputBox("end time")
    
         With Sheets("AutoFilterTest")
    
             If cells.AutoFilter Then
    
                cells.AutoFilter
    
             End If
    
             .Columns("B:B").NumberFormat = "dd-MM-yyyy hh:mm"
    
             LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
             LastCol = .cells(1, Columns.Count).End(xlToLeft).Column
    
             starttime = DateValue(stime) + TimeValue(stime)
    
             starttimestr = Format(starttime, "MM/dd/yyyy hh:mm")
    
             endtime = DateValue(etime) + TimeValue(etime)
    
             endtimestr = Format(endtime, "MM/dd/yyyy hh:mm")
    
             .Range(.Range("A1"), .cells(LastRow, LastCol)).AutoFilter _
    
                Field:=2, _
    
                Criteria1:=">=" & starttimestr, _
    
                Operator:=xlAnd, _
    
                Criteria2:="<=" & endtimestr
    
              Set selectedCells = .Range(.Range("A1"), .cells(LastRow, LastCol)).SpecialCells(xlCellTypeVisible)
    
         End With
    
         With Sheets("AutoFilterTestCopy")
    
            selectedCells.Copy _
    
               Destination:=.Range("A1")
    
         End With
    
     End Sub
    

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by GyTasS Wednesday, December 17, 2014 9:55 AM
    Wednesday, December 17, 2014 9:53 AM
    Moderator

All replies

  • Hello,

    Are you coding in VB.NET or within Excel using VBA code?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, December 16, 2014 12:10 AM
  • Hi,

    Excel using VBA code...

    Tuesday, December 16, 2014 6:10 AM
  • Hi GyTasS,

    This forum is to discuss problems of VB. Your question is related to VBA.

    We are moving it to Excel forum for better supports. Thanks.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Best regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Youjun Tang Tuesday, December 16, 2014 8:39 AM
    Tuesday, December 16, 2014 8:39 AM
  • Hi GyTasS,

    Base on my test in Excel 2013, it seems the issue in excel. I tried record macro and run that macro, it even doesn’t work (for Danish)!

    To deal with that issue, I change the format (for input date) to “MM/dd/yyyy hh:mm” and it works fine. Please try it and check whether it works.

    Sub selectrange()
    
         Dim LastRow As Integer
    
         Dim LastCol As Integer
    
         Dim starttime As Date
    
         Dim starttimestr As String
    
         Dim endtime As Date
    
         Dim endtimestr As String
    
         Dim selectedCells As Range
    
         Dim stime As String
    
         Dim etime As String
    
         stime = InputBox("start time")
    
         etime = InputBox("end time")
    
         With Sheets("AutoFilterTest")
    
             If cells.AutoFilter Then
    
                cells.AutoFilter
    
             End If
    
             .Columns("B:B").NumberFormat = "dd-MM-yyyy hh:mm"
    
             LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
             LastCol = .cells(1, Columns.Count).End(xlToLeft).Column
    
             starttime = DateValue(stime) + TimeValue(stime)
    
             starttimestr = Format(starttime, "MM/dd/yyyy hh:mm")
    
             endtime = DateValue(etime) + TimeValue(etime)
    
             endtimestr = Format(endtime, "MM/dd/yyyy hh:mm")
    
             .Range(.Range("A1"), .cells(LastRow, LastCol)).AutoFilter _
    
                Field:=2, _
    
                Criteria1:=">=" & starttimestr, _
    
                Operator:=xlAnd, _
    
                Criteria2:="<=" & endtimestr
    
              Set selectedCells = .Range(.Range("A1"), .cells(LastRow, LastCol)).SpecialCells(xlCellTypeVisible)
    
         End With
    
         With Sheets("AutoFilterTestCopy")
    
            selectedCells.Copy _
    
               Destination:=.Range("A1")
    
         End With
    
     End Sub
    

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by GyTasS Wednesday, December 17, 2014 9:55 AM
    Wednesday, December 17, 2014 9:53 AM
    Moderator
  • Yeap, now it works perfect!
    Wednesday, December 17, 2014 9:55 AM