locked
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!

    Monday, December 15, 2014 10:07 PM

Answers

  • Try using this for starttime and other variables:

    starttime = CDbl(CDate(DateSerial(Year(stime), Month(stime), Day(stime)) _
    + TimeSerial(Hour(stime), Minute(stime), Second(stime))))
    


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by GyTasS Wednesday, December 17, 2014 7:43 AM
    Tuesday, December 16, 2014 8:54 PM

All replies

  • Try using this for starttime and other variables:

    starttime = CDbl(CDate(DateSerial(Year(stime), Month(stime), Day(stime)) _
    + TimeSerial(Hour(stime), Minute(stime), Second(stime))))
    


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by GyTasS Wednesday, December 17, 2014 7:43 AM
    Tuesday, December 16, 2014 8:54 PM
  • excellent! Now it works! Thanks ;-)
    Wednesday, December 17, 2014 7:43 AM