Was meinst du mit "filtern" - "Spalten ausblenden"?
Unter dem Kriterium, dass ab T16 bis TZ16 die Tage nacheinander und unterbrechungsfrei aufgelistet sind:
(Du musst in der Funktion GetDateRange() möglicherweise den Tabellennamen ändern, sodass er bei dir passt.)
Option Explicit
Public Sub Demo()
Dim rngT As Excel.Range
Dim rngR As Excel.Range
If GetDateRange("2018-05-05", "2018-05-10", rngR, rngT) Then
'apply filter
rngT.Columns.Hidden = True
rngR.Columns.Hidden = False
Call Application.Goto(rngR(1), True)
Else
Debug.Print Format$(Time, "\#hh:mm:ss\#"); Spc(2); "GetDateRange() - invalid date range"
End If
End Sub
'////////////////////////////////////////////////////////////////
'// [in:]
'// StartDate
'// EndDate
'//
'// [out:]
'// DRange ... filtered date range
'// TRange ... date range (DRange is a part of it)
'//
'// [return:]
'// 'True' if sucessfull, 'False' if not
Public Function GetDateRange(ByVal StartDate As Date, ByVal EndDate As Date, ByRef DRange As Excel.Range, Optional ByRef TRange As Excel.Range) As Boolean
Const C_WKS_NAME As String = "Tabelle1" '<- mod.
Const C_RNG_STR As String = "T16:TZ16"
If StartDate > EndDate Then Exit Function
Dim rng As Excel.Range
Dim rngRange As Excel.Range
Set rng = Worksheets(C_WKS_NAME).Range(C_RNG_STR)
Set rngRange = rng(1).Offset(ColumnOffset:=DateDiff("d", CDate(rng(1)), StartDate))
Set rngRange = rngRange.Resize(ColumnSize:=1 + DateDiff("d", StartDate, EndDate))
Set TRange = rng
Set DRange = rngRange
GetDateRange = True
End Function
Grüße
|