VBA Code to Hide Rows based on Date Range

M

Marklarbear

Board Regular
Joined
Nov 6, 2003
Messages
119
Office Version
  1. 365
Platform
  1. Windows
HI Guys - i'm looking for code that will hide rows based on a date range.
In cell U1 is the start date of the range. In cell X1 is the end date of the range.

What I want to do is assign a macro to a "Filter by date Range" button (Cell Z1) - once the dates are entered and button selected - to hide all rows of data where the dates in column I fall outside of the range (keeping the headings and blank rows).

ie date range: 21/06/21 - 27/06/21

Rows hidden - 4-12, 20-31, 34-42, 50-61

and so on....

or date range: 01/07/21 - 03/07/21

Rows hidden - 4-22, 26-31, 34-52, 56-61


and so on.....

I have 3 data sets of data in my example (Skill 1, Skill 2, skill 3) however in my live doc there could be anywhere up to 60 skill sets of data going down the sheet.

Any kind of help will be greatly appreciated.....

MrExcel Example 1.01.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Previous YearCurrent YearStart Date21/06/2021End Date27/06/2021
2Actual offeredAverage DOWActual FactorForecastAverage DOWActual FactorHol FactorFactor Diff YYYY v YYYYsuggested factor change Factor conversion to calls
3Skill 1AVERAGE DAY NOT COMPLETEDSkill 1AVERAGE DAY NOT COMPLETEDComments
4Sat-6-Jun-20202020-06-06Saturday9110686%Sat-12-Jun-2021Saturday1110%86%0
5Sun-7-Jun-20202020-06-07Sunday506084%Sun-13-Jun-2021Sunday550%84%0
6Mon-8-Jun-20202020-06-08Monday9834329%Mon-14-Jun-2021Monday3810%29%0
7Tue-9-Jun-20202020-06-09Tuesday388362107%Tue-15-Jun-2021Tuesday3840%107%0
8Wed-10-Jun-20202020-06-10Wednesday396352113%Wed-16-Jun-2021Wednesday3770%113%0
9Thu-11-Jun-20202020-06-11Thursday392343114%Thu-17-Jun-2021Thursday3790%114%0
10Fri-12-Jun-20202019-06-08Friday3510%Fri-18-Jun-2021Friday3430%0%0
11Sat-13-Jun-20202019-06-09Saturday1060%Sat-19-Jun-2021Saturday1110%0%0
12Sun-14-Jun-20202019-06-10Sunday600%Sun-20-Jun-2021Sunday550%0%0
13Mon-15-Jun-20202019-06-11Monday3430%Mon-21-Jun-2021Monday3810%0%0
14Tue-16-Jun-20202019-06-12Tuesday3620%Tue-22-Jun-2021Tuesday3840%0%0
15Wed-17-Jun-20202019-06-13Wednesday3520%Wed-23-Jun-2021Wednesday3770%0%0
16Thu-18-Jun-20202019-06-14Thursday3430%Thu-24-Jun-2021Thursday3790%0%0
17Fri-19-Jun-20202019-06-15Friday3510%Fri-25-Jun-2021Friday3430%0%0
18Sat-20-Jun-20202019-06-16Saturday1060%Sat-26-Jun-2021Saturday1110%0%0
19Sun-21-Jun-20202019-06-17Sunday600%Sun-27-Jun-2021Sunday550%0%0
20Mon-22-Jun-20202019-06-18Monday3430%Mon-28-Jun-2021Monday3810%0%0
21Tue-23-Jun-20202019-06-19Tuesday3620%Tue-29-Jun-2021Tuesday3840%0%0
22Wed-24-Jun-20202019-06-20Wednesday3520%Wed-30-Jun-2021Wednesday3770%0%0
23Thu-25-Jun-20202019-06-21Thursday3430%Thu-1-Jul-2021Thursday3790%0%0
24Fri-26-Jun-20202019-06-22Friday3510%Fri-2-Jul-2021Friday3430%0%0
25Sat-27-Jun-20202019-06-23Saturday1060%Sat-3-Jul-2021Saturday1110%0%0
26Sun-28-Jun-20202019-06-24Sunday600%Sun-4-Jul-2021Sunday550%0%0
27Mon-29-Jun-20202019-06-25Monday3430%Mon-5-Jul-2021Monday3810%0%0
28Tue-30-Jun-20202019-06-26Tuesday3620%Tue-6-Jul-2021Tuesday3840%0%0
29Wed-1-Jul-20202019-06-27Wednesday3520%Wed-7-Jul-2021Wednesday3770%0%0
30Thu-2-Jul-20202019-06-28Thursday3430%Thu-8-Jul-2021Thursday3790%0%0
31Fri-3-Jul-20202019-06-29Friday3510%Fri-9-Jul-2021Friday3430%0%0
32
33Skill 2AVERAGE DAY NOT COMPLETEDSkill 2AVERAGE DAY NOT COMPLETED
34Sat-6-Jun-20202020-06-06Saturday444599%Sat-12-Jun-2021Saturday570%99%0
35Sun-7-Jun-20202020-06-07Sunday151791%Sun-13-Jun-2021Sunday290%91%0
36Mon-8-Jun-20202020-06-08Monday7215546%Mon-14-Jun-2021Monday1830%46%0
37Tue-9-Jun-20202020-06-09Tuesday143140102%Tue-15-Jun-2021Tuesday1550%102%0
38Wed-10-Jun-20202020-06-10Wednesday149142105%Wed-16-Jun-2021Wednesday1490%105%0
39Thu-11-Jun-20202020-06-11Thursday173138125%Thu-17-Jun-2021Thursday1420%125%0
40Fri-12-Jun-20202019-06-08Friday1430%Fri-18-Jun-2021Friday1510%0%0
41Sat-13-Jun-20202019-06-09Saturday450%Sat-19-Jun-2021Saturday570%0%0
42Sun-14-Jun-20202019-06-10Sunday170%Sun-20-Jun-2021Sunday290%0%0
43Mon-15-Jun-20202019-06-11Monday1550%Mon-21-Jun-2021Monday1830%0%0
44Tue-16-Jun-20202019-06-12Tuesday1400%Tue-22-Jun-2021Tuesday1550%0%0
45Wed-17-Jun-20202019-06-13Wednesday1420%Wed-23-Jun-2021Wednesday1490%0%0
46Thu-18-Jun-20202019-06-14Thursday1380%Thu-24-Jun-2021Thursday1420%0%0
47Fri-19-Jun-20202019-06-15Friday1430%Fri-25-Jun-2021Friday1510%0%0
48Sat-20-Jun-20202019-06-16Saturday450%Sat-26-Jun-2021Saturday570%0%0
49Sun-21-Jun-20202019-06-17Sunday170%Sun-27-Jun-2021Sunday290%0%0
50Mon-22-Jun-20202019-06-18Monday1550%Mon-28-Jun-2021Monday1830%0%0
51Tue-23-Jun-20202019-06-19Tuesday1400%Tue-29-Jun-2021Tuesday1550%0%0
52Wed-24-Jun-20202019-06-20Wednesday1420%Wed-30-Jun-2021Wednesday1490%0%0
53Thu-25-Jun-20202019-06-21Thursday1380%Thu-1-Jul-2021Thursday1420%0%0
54Fri-26-Jun-20202019-06-22Friday1430%Fri-2-Jul-2021Friday1510%0%0
55Sat-27-Jun-20202019-06-23Saturday450%Sat-3-Jul-2021Saturday570%0%0
56Sun-28-Jun-20202019-06-24Sunday170%Sun-4-Jul-2021Sunday290%0%0
57Mon-29-Jun-20202019-06-25Monday1550%Mon-5-Jul-2021Monday1830%0%0
58Tue-30-Jun-20202019-06-26Tuesday1400%Tue-6-Jul-2021Tuesday1550%0%0
59Wed-1-Jul-20202019-06-27Wednesday1420%Wed-7-Jul-2021Wednesday1490%0%0
60Thu-2-Jul-20202019-06-28Thursday1380%Thu-8-Jul-2021Thursday1420%0%0
61Fri-3-Jul-20202019-06-29Friday1430%Fri-9-Jul-2021Friday1510%0%0
62
63Skill 3AVERAGE DAY NOT COMPLETEDSkill 3AVERAGE DAY NOT COMPLETED
64Sat-6-Jun-20202020-06-06Saturday000%Sat-12-Jun-2021Saturday00%0%0
65Sun-7-Jun-20202020-06-07Sunday000%Sun-13-Jun-2021Sunday00%0%0
66Mon-8-Jun-20202020-06-08Monday8412368%Mon-14-Jun-2021Monday00%68%0
67Tue-9-Jun-20202020-06-09Tuesday170114149%Tue-15-Jun-2021Tuesday00%149%0
68Wed-10-Jun-20202020-06-10Wednesday134126107%Wed-16-Jun-2021Wednesday00%107%0
69Thu-11-Jun-20202020-06-11Thursday150130116%Thu-17-Jun-2021Thursday00%116%0
70Fri-12-Jun-20202019-06-08Friday1380%Fri-18-Jun-2021Friday00%0%0
71Sat-13-Jun-20202019-06-09Saturday00%Sat-19-Jun-2021Saturday00%0%0
72Sun-14-Jun-20202019-06-10Sunday00%Sun-20-Jun-2021Sunday00%0%0
73Mon-15-Jun-20202019-06-11Monday1230%Mon-21-Jun-2021Monday00%0%0
74Tue-16-Jun-20202019-06-12Tuesday1140%Tue-22-Jun-2021Tuesday00%0%0
75Wed-17-Jun-20202019-06-13Wednesday1260%Wed-23-Jun-2021Wednesday00%0%0
76Thu-18-Jun-20202019-06-14Thursday1300%Thu-24-Jun-2021Thursday00%0%0
77Fri-19-Jun-20202019-06-15Friday1380%Fri-25-Jun-2021Friday00%0%0
78Sat-20-Jun-20202019-06-16Saturday00%Sat-26-Jun-2021Saturday00%0%0
79Sun-21-Jun-20202019-06-17Sunday00%Sun-27-Jun-2021Sunday00%0%0
80Mon-22-Jun-20202019-06-18Monday1230%Mon-28-Jun-2021Monday00%0%0
81Tue-23-Jun-20202019-06-19Tuesday1140%Tue-29-Jun-2021Tuesday00%0%0
82Wed-24-Jun-20202019-06-20Wednesday1260%Wed-30-Jun-2021Wednesday00%0%0
83Thu-25-Jun-20202019-06-21Thursday1300%Thu-1-Jul-2021Thursday00%0%0
84Fri-26-Jun-20202019-06-22Friday1380%Fri-2-Jul-2021Friday00%0%0
85Sat-27-Jun-20202019-06-23Saturday00%Sat-3-Jul-2021Saturday00%0%0
86Sun-28-Jun-20202019-06-24Sunday00%Sun-4-Jul-2021Sunday00%0%0
87Mon-29-Jun-20202019-06-25Monday1230%Mon-5-Jul-2021Monday00%0%0
88Tue-30-Jun-20202019-06-26Tuesday1140%Tue-6-Jul-2021Tuesday00%0%0
89Wed-1-Jul-20202019-06-27Wednesday1260%Wed-7-Jul-2021Wednesday00%0%0
90Thu-2-Jul-20202019-06-28Thursday1300%Thu-8-Jul-2021Thursday00%0%0
91Fri-3-Jul-20202019-06-29Friday1380%Fri-9-Jul-2021Friday00%0%0
92
93
94
95
Forecast Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X1Cell Value=" - "textNO
I4Cell Value=" - "textNO
B33Cell Value=" - "textNO
B38:B39Cell Value=" - "textNO
B34:B37,B40:B62Cell Value=" - "textNO
B68:B69Cell Value=" - "textNO
B10Cell Value=" - "textNO
B9Cell Value=" - "textNO
B3:B8,B70:B92,B11:B32,B63:B67Cell Value=" - "textNO
B1:B2Cell Value=" - "textNO
C91Cell Value=" - "textNO
C71:C91Cell Value=" - "textNO
C70Cell Value=" - "textNO
C69Cell Value=" - "textNO
C64:C68Cell Value=" - "textNO
L63Cell Value=" - "textNO
L33Cell Value=" - "textNO
L3Cell Value=" - "textNO
E3Cell Value=" - "textNO
E33Cell Value=" - "textNO
E63Cell Value=" - "textNO
E34:E61Cell Value=0textNO
E34:E61Cell Value=" - "textNO
E34:E61Cell Value=" - "textNO
E34:E61Cell Value=0textNO
E34:E61Cell Value=" - "textNO
M91Cell Value=0textNO
M91Cell Value=" - "textNO
M91Cell Value=" - "textNO
F91Cell Value=0textNO
F91Cell Value=" - "textNO
E91Cell Value=0textNO
E91Cell Value=" - "textNO
E91:F91Cell Value=" - "textNO
K91Cell Value=" - "textNO
K91Cell Value=" - "textNO
D91Cell Value=" - "textNO
D91Cell Value=" - "textNO
N91Cell Value=" - "textNO
I91:J91Cell Value=" - "textNO
G91:H91Cell Value=" - "textNO
M71:M91Cell Value=0textNO
M71:M91Cell Value=" - "textNO
M71:M91Cell Value=" - "textNO
F71:F90Cell Value=0textNO
F71:F90Cell Value=" - "textNO
E71:E90Cell Value=0textNO
E71:E90Cell Value=" - "textNO
E71:F90Cell Value=" - "textNO
K71:K91Cell Value=" - "textNO
K71:K91Cell Value=" - "textNO
D71:D90Cell Value=" - "textNO
D71:D90Cell Value=" - "textNO
N71:N91Cell Value=" - "textNO
I71:J91Cell Value=" - "textNO
G71:H90Cell Value=" - "textNO
M70Cell Value=0textNO
M70Cell Value=" - "textNO
M70Cell Value=" - "textNO
F70Cell Value=0textNO
F70Cell Value=" - "textNO
E70Cell Value=0textNO
E70Cell Value=" - "textNO
E70:F70Cell Value=" - "textNO
K70Cell Value=" - "textNO
K70Cell Value=" - "textNO
D70Cell Value=" - "textNO
D70Cell Value=" - "textNO
N70Cell Value=" - "textNO
I70:J70Cell Value=" - "textNO
G70:H70Cell Value=" - "textNO
M69Cell Value=0textNO
M69Cell Value=" - "textNO
M69Cell Value=" - "textNO
F69Cell Value=0textNO
F69Cell Value=" - "textNO
E69Cell Value=0textNO
E69Cell Value=" - "textNO
E69:F69Cell Value=" - "textNO
K69Cell Value=" - "textNO
K69Cell Value=" - "textNO
D69Cell Value=" - "textNO
D69Cell Value=" - "textNO
N69Cell Value=" - "textNO
I69:J69Cell Value=" - "textNO
G69:H69Cell Value=" - "textNO
M61Cell Value=0textNO
M61Cell Value=" - "textNO
F61Cell Value=0textNO
F61Cell Value=" - "textNO
F61Cell Value=" - "textNO
K61Cell Value=" - "textNO
K61Cell Value=" - "textNO
D61Cell Value=" - "textNO
N61Cell Value=" - "textNO
C61:D61,G61:J61Cell Value=" - "textNO
M41:M61Cell Value=0textNO
M41:M61Cell Value=" - "textNO
F41:F61Cell Value=0textNO
F41:F61Cell Value=" - "textNO
F41:F61Cell Value=" - "textNO
K41:K61Cell Value=" - "textNO
K41:K61Cell Value=" - "textNO
D41:D61Cell Value=" - "textNO
N41:N61Cell Value=" - "textNO
G41:J60,G61,C41:D61,I61:J61Cell Value=" - "textNO
M40Cell Value=0textNO
M40Cell Value=" - "textNO
F40Cell Value=0textNO
F40Cell Value=" - "textNO
F40Cell Value=" - "textNO
K40Cell Value=" - "textNO
K40Cell Value=" - "textNO
D40Cell Value=" - "textNO
N40Cell Value=" - "textNO
C40:D40,G40:J40Cell Value=" - "textNO
M39Cell Value=0textNO
M39Cell Value=" - "textNO
F39Cell Value=0textNO
F39Cell Value=" - "textNO
F39Cell Value=" - "textNO
K39Cell Value=" - "textNO
K39Cell Value=" - "textNO
D39Cell Value=" - "textNO
N39Cell Value=" - "textNO
C39:D39,G39:J39Cell Value=" - "textNO
M31Cell Value=0textNO
M31Cell Value=" - "textNO
K31Cell Value=" - "textNO
F31Cell Value=0textNO
F31Cell Value=" - "textNO
E31Cell Value=0textNO
E31Cell Value=" - "textNO
D31Cell Value=" - "textNO
I31:K31Cell Value=" - "textNO
D31:H31Cell Value=" - "textNO
M11:M30Cell Value=0textNO
M11:M30Cell Value=" - "textNO
K11:K31Cell Value=" - "textNO
F11:F30Cell Value=0textNO
F11:F30Cell Value=" - "textNO
E11:E30Cell Value=0textNO
E11:E30Cell Value=" - "textNO
D11:D31Cell Value=" - "textNO
I11:K31Cell Value=" - "textNO
D11:H30,D31Cell Value=" - "textNO
M10Cell Value=0textNO
M10Cell Value=" - "textNO
K10Cell Value=" - "textNO
F10Cell Value=0textNO
F10Cell Value=" - "textNO
E10Cell Value=0textNO
E10Cell Value=" - "textNO
D10Cell Value=" - "textNO
I10:K10Cell Value=" - "textNO
D10:H10Cell Value=" - "textNO
M9Cell Value=0textNO
M9Cell Value=" - "textNO
K9Cell Value=" - "textNO
F9Cell Value=0textNO
F9Cell Value=" - "textNO
E9Cell Value=0textNO
E9Cell Value=" - "textNO
D9Cell Value=" - "textNO
I9:K9Cell Value=" - "textNO
D9:H9Cell Value=" - "textNO
M8Cell Value=0textNO
M8Cell Value=" - "textNO
K8Cell Value=" - "textNO
F8Cell Value=0textNO
F8Cell Value=" - "textNO
E8Cell Value=0textNO
E8Cell Value=" - "textNO
D8Cell Value=" - "textNO
I8:K8Cell Value=" - "textNO
D8:H8Cell Value=" - "textNO
M64:M68Cell Value=0textNO
M64:M68Cell Value=" - "textNO
M64:M68Cell Value=" - "textNO
M34:M38Cell Value=0textNO
M34:M38Cell Value=" - "textNO
F64:F91Cell Value=0textNO
F64:F91Cell Value=" - "textNO
E64:E91Cell Value=0textNO
E64:E91Cell Value=" - "textNO
E65:F68Cell Value=" - "textNO
F34:F38Cell Value=0textNO
F34:F38Cell Value=" - "textNO
F35:F38Cell Value=" - "textNO
K64:K68Cell Value=" - "textNO
K65:K68Cell Value=" - "textNO
K34:K38Cell Value=" - "textNO
K35:K38Cell Value=" - "textNO
D64:D91Cell Value=" - "textNO
D65:D68Cell Value=" - "textNO
D34:D38Cell Value=" - "textNO
C4:C31Cell Value=" - "textNO
M4:M31Cell Value=0textNO
M4:M31Cell Value=" - "textNO
K4:K7Cell Value=" - "textNO
F4:F31Cell Value=0textNO
F4:F31Cell Value=" - "textNO
E4:E31Cell Value=0textNO
E4:E31Cell Value=" - "textNO
D4:D7Cell Value=" - "textNO
I64:J68,L64Cell Value=" - "textNO
I5:K7,J4Cell Value=" - "textNO
F3:K3,G4:H4,L4,D5:H7,G64:H68,F33:K33,F63:K63,G34:J38,A3:A31,G8:G31,G69:G91,A63:A91,A33:A61,C33:D33,C63:D63,C34,C3:D3,C35:D38Cell Value=" - "textNO
T6Cell Value=" - "textNO
T3:T5Cell Value=" - "textNO
U3:XFD31,S3:S31,S32:XFD92,A1:A2,A32,A62,A92,L34:M61,L65:M91,L5:R31,C92:R92,C62:R62,C32:R32,C2:XFD2,M3:R3,N4:R4,O69:R91,M33:R33,M63:R63,N34:R38,O39:R61,N64:R68,93:1048576,C1:U1,W1,Z1:XFD1Cell Value=" - "textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try this in a standard module:
VBA Code: 
Sub HideRowsBasedOnDates()
    Dim i As Long, lr As Long, d1 As String, d2 As String, dateInCell As String
   
    With Sheets("Forecast Data")
   
        .Rows.Hidden = False
        On Error GoTo errHandler
        d1 = DateValue(.Cells(1, "U").Value)
        d2 = DateValue(.Cells(1, "X").Value)
        On Error GoTo 0
        lr = .Cells(Rows.Count, "I").End(xlUp).Row
       
        If d1 > d2 Then
            MsgBox "Invalid Start Date and/or End Date", vbExclamation, "Error"
            Exit Sub
        End If
       
        Application.ScreenUpdating = False
        For i = 4 To lr
            If InStr(.Cells(i, "I").Value, "-") > 0 Then
                dateInCell = DateValue(Split(Cells(i, "I").Value, "-")(1) & "/" & Month("1/" & Split(Cells(i, "I").Value, "-")(2)) & "/" & Split(Cells(i, "I").Value, "-")(3))
                If Not (d1 <= dateInCell And dateInCell <= d2) Then
                    Rows(i).Hidden = True
                End If
            End If
        Next i
        Application.ScreenUpdating = True
       
    End With
Exit Sub

errHandler:
    MsgBox "Invalid Start Date and/or End Date", vbExclamation, "Error"
    Exit Sub
   
End Sub

Sub ShowAllRows()
    Sheets("Forecast Data").Rows.Hidden = False
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Calculate Backdated dates
Replies
5
Views
268
Joe4
Joe4
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
452
amolvijay
A
S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
192
RobP
R
vinzent
  • Question Question
Formula to get the 4th workday before end of month
Replies
8
Views
422
Tetra201
Tetra201
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top