Hide rows between date range using todays date.

J

jjwprotozoa

New Member
Joined
Apr 1, 2012
Messages
5
Hi,

I am using excel 2010 and looking for a formula or VBA macro for the following:

<table width="418" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:7753;width:159pt" width="212"> <col style="mso-width-source:userset;mso-width-alt:7533;width:155pt" width="206"> </colgroup><tbody><tr><td valign="top">
</td><td valign="top" align="center">J
</td><td valign="top" align="center">K
</td></tr><tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">1</td><td style="height:13.5pt;width:159pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:#4F81BD;mso-pattern:#4F81BD none" width="212" height="18">Start Date of Leave
</td> <td style="width:155pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="206">End Date of Leave
</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">2
</td><td class="xl65" style="height:13.5pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="18">3/11/2012</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">3
</td><td class="xl65" style="height:13.5pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none" align="right" height="18">4/2/2012</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">4/3/2012</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">4
</td><td class="xl65" style="height:13.5pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="18">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">5
</td><td class="xl65" style="height:13.5pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none" align="right" height="18">2/22/2012</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">2/24/2012</td> </tr> <tr style="mso-height-source:userset;height:13.5pt" height="18"> <td valign="top">6
</td><td class="xl65" style="height:13.5pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="18">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> </tbody></table>

As you can see above each row has a start date in column J and an end date in column K. Some rows in both columns are blank or the end date column is blank. I want to hide the row if today's date, the date when I run the macro, is between or equal to these two dates. And I need the row to show again one day before the end date.

I have tried using the following, but not getting the results I need.

Sub HideRows()
Dim r as Range, cell as Range
activesheet.Rows.EntireRow.
<wbr>Hidden = False
with Activesheet
set r = .Range(.cells(2,"B"),.cells(<wbr>rows.count,"B").End(xlup))
for each cell in r
if cell.value <= date and date <= cell.offset(0,1).value then
cell.EntireRow.Hidden = True
end if
Next
End With
End Sub

​
This is for a company transport list. I need the employees on leave to be removed from the list if they are on leave and need them to be added to the list when they return from leave.
Please let me know if I need to be more specific.

Thanks in advance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try something like this...

Code: 
Sub Hide_Leave_Rows()
    Dim cell As Range
    For Each cell In Range("J2", Range("J" & Rows.Count).End(xlUp))
        If cell.Value > 0 And cell.Offset(, 1).Value > 0 Then
            cell.EntireRow.Hidden = (Date >= cell.Value And Date + 1 < cell.Offset(, 1).Value)
        End If
    Next cell
End Sub
 
Upvote 0
try this macro


Code: 
Sub test()
Dim r As Range, c As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then
               LastRow = Cells.Find(What:="*", After:=[a1], _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
                LastColumn = Cells.Find(What:="*", After:=[a1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
                'MsgBox Cells(LastRow, LastColumn).Address
    
    Set LastCell = Cells(LastRow, LastColumn)
    End If
    Set r = Range(Range("a1"), Cells(LastCell.Row, 1))
    
For Each c In r
If c = "" Then GoTo nextc
If c <= CDate(Date) And c >= CDate(Date) Then
c.EntireRow.Hidden = True
End If
nextc:
Next c

End Sub


Code: 
Sub undo()
Range(Range("a1"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Hidden = False
End Sub
 
Upvote 0
Hi,

I have tried both these recommendations and have not notices any change. I have even added in some trial dates and this has not worked either. Any suggestions? (I'm not an expert at VBA)
 
Upvote 0
your data is like this


Excel Workbook
AB
1start dateend date
23/11/2012
3
44/2/20124/3/2012
5
62/22/20122/24/2012
Sheet1


now I have modified the macro


Code: 
Sub test()
Dim r As Range, c As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Worksheets("sheet1").Activate
If WorksheetFunction.CountA(Cells) > 0 Then
               LastRow = Cells.Find(What:="*", After:=[a1], _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
                LastColumn = Cells.Find(What:="*", After:=[a1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
                'MsgBox Cells(LastRow, LastColumn).Address
    
    Set LastCell = Cells(LastRow, LastColumn)
    End If
    Set r = Range(Range("a1"), Cells(LastCell.Row, 1))
    
For Each c In r
If c = "" Or c.Offset(0, 1) = "" Then GoTo nextc
If c <= CDate(Date) And c.Offset(0, 1) >= CDate(Date) Then
c.EntireRow.Hidden = True
End If
nextc:
Next c

End Sub

Code: 
Sub undo()
Range(Range("a1"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Hidden = False
End Sub

now run the macro test . the row no. 4 will be hidden.

now run undo the data is back to original status;. (no row hidden)

I hope how to park the macros in the module in vb editor and run it.
 
Upvote 0
This is column J and K in my spreadsheet.
I have tried the new macro code and see no changes :(
I have tried modifying the code to the columns that I am using as you used A and B.



<table width="418" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:7753;width:159pt" width="212"> <col style="mso-width-source:userset;mso-width-alt:7533;width:155pt" width="206"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:159pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:#4F81BD;mso-pattern:#4F81BD none" width="212" height="20">Start Date of Leave, SSS or PSS</td> <td style="width:155pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="206">End Date of Leave, SSS or PSS</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right" height="20">3/11/2012</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="height:60.0pt" height="80"> <td class="xl65" style="height:60.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="80">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">
</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="40">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:yellow;mso-pattern:black none" align="right" height="20">4/2/2012</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:yellow; mso-pattern:black none" align="right">4/3/2012</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:yellow;mso-pattern:black none" align="right" height="20">2/22/2012</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:yellow; mso-pattern:black none" align="right">4/2/2012</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:yellow;mso-pattern:black none" align="right" height="20">2/22/2012</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:yellow; mso-pattern:black none" align="right">4/1/2012</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">
</td> </tr> </tbody></table>
 
Upvote 0
this information was not given in your first message.

I am modifying portion of the macro starting form "for each c in r"
try and post feedbck


Code: 
For Each c In r
'If c = "" Or c.Offset(0, 1) = "" Then GoTo nextc

If Cells(c.ow, "J") = "" Or Cells(c.Row, "K") - "" Then GoTo nextc
'If c <= CDate(Date) And c.Offset(0, 1) >= CDate(Date) Then
If Cells(c.Row, "J") <= CDate(Date) And Cells(c.Row, "K") >= CDate(Date) Then

c.EntireRow.Hidden = True
End If
nextc:
Next c
 
Upvote 0
venkat1926:

Thanks, you had some typo's in your code but I just amended them and it worked. Did exactly what I needed it to. As you commented in your previous message I just need to figure out how to join the two codes together. Any ideas on that?

Amendments to code in red bellow:

Code: 
For Each c In r
'If c = "" Or c.Offset(0, 1) = "" Then GoTo nextc

If Cells(c.[COLOR=Red]ow[/COLOR], "J") = "" Or Cells(c.Row, "K") [COLOR=Red]-[/COLOR] "" Then GoTo nextc
'If c <= CDate(Date) And c.Offset(0, 1) >= CDate(Date) Then
If Cells(c.Row, "J") <= CDate(Date) And Cells(c.Row, "K") >= CDate(Date) Then

c.EntireRow.Hidden = True
End If
nextc:
Next c
Awesome work :biggrin:
 
Upvote 0
I always prefer separate macro(or call them sub macros). It is easy to debug separately. you can make these code statements into a separate macro make a mins macro and call of these macros SUCCESSIVELY. you have to defind(not dim) r .

take all the dim statement to top above all the macros so that all the procedures (subs) can use those dim statements within the module
 
Upvote 0
venkat1926 said:
I always prefer separate macro(or call them sub macros). It is easy to debug separately. you can make these code statements into a separate macro make a mins macro and call of these macros SUCCESSIVELY. you have to defind(not dim) r .

take all the dim statement to top above all the macros so that all the procedures (subs) can use those dim statements within the module
Click to expand...
Thanks for all your help. It has been very useful. And after all this it turns out that I do not need this function after all as my company has decided to remove the two columns in question. I will save this macro for future use though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
706
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,156
Messages
6,183,239
Members
453,152
Latest member
ChrisMd

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