Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows based on a date function
I have a spread sheet which can vary from 8k to 12k lines long.
Row 1 contains header information. Column Y can contain either blank cells, or cells with dates. Excel 2003. I need to run through all the rows, look at the value in column Y, and determine if it is more than 100 days from todays date. "today" meaning whatever the date is when the macro is run. If the date in Y is more than 100 days from todays date, I need to hide the entire row. Can anybody provide help with this? Many thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows based on a date function
Hi,
You don't say whether it's 100 days in the future or the past so this assumes the future. Right click your sheet tab, view code and paste this in and run it. Sub Marine() Dim MyRange, MyRange1 As Range lastrow = Cells(Rows.Count, "Y").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("Y2:Y" & lastrow) For Each c In MyRange If c.Value Now + 100 Then 'Change to suit If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.EntireRow.Hidden = True End If End Sub Mike "Melvin Purvis" wrote: I have a spread sheet which can vary from 8k to 12k lines long. Row 1 contains header information. Column Y can contain either blank cells, or cells with dates. Excel 2003. I need to run through all the rows, look at the value in column Y, and determine if it is more than 100 days from todays date. "today" meaning whatever the date is when the macro is run. If the date in Y is more than 100 days from todays date, I need to hide the entire row. Can anybody provide help with this? Many thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows based on a date function
I left the sheet range in use this instead
Sub Marine() Dim MyRange, MyRange1 As Range lastrow = Cells(Rows.Count, "Y").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("Y2:Y" & lastrow) For Each c In MyRange If c.Value Now + 100 Then 'Change to suit If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.EntireRow.Hidden = True End If End Sub Mike "Mike H" wrote: Hi, You don't say whether it's 100 days in the future or the past so this assumes the future. Right click your sheet tab, view code and paste this in and run it. Sub Marine() Dim MyRange, MyRange1 As Range lastrow = Cells(Rows.Count, "Y").End(xlUp).Row Set MyRange = Sheets("Sheet1").Range("Y2:Y" & lastrow) For Each c In MyRange If c.Value Now + 100 Then 'Change to suit If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.EntireRow.Hidden = True End If End Sub Mike "Melvin Purvis" wrote: I have a spread sheet which can vary from 8k to 12k lines long. Row 1 contains header information. Column Y can contain either blank cells, or cells with dates. Excel 2003. I need to run through all the rows, look at the value in column Y, and determine if it is more than 100 days from todays date. "today" meaning whatever the date is when the macro is run. If the date in Y is more than 100 days from todays date, I need to hide the entire row. Can anybody provide help with this? Many thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows based on a date function
Try this: Sub hide_rows() Dim i As Long For i = Range("Y" & Rows.Count).End(xlUp).Row To 2 Step -1 If Range("Y" & i).Value < "" And Range("Y" & i) <= Date - 100 Then Range("Y" & i).EntireRow.Hidden = True End If Next i End Sub -- Breakfast Guy Regards, Breakfast Guy Forum Moderator www.thecodecage.com ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23938 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows based on a date function
Just a note on whether the 100 days is in the past or in the future. If in
the past, both suggestions will work fine. But for in the future, all the rows would need to be unhidden first, so that those rows that have been previously hidden as over 100 days, can be retested. Mike F "Breakfast Guy" wrote in message ... Try this: Sub hide_rows() Dim i As Long For i = Range("Y" & Rows.Count).End(xlUp).Row To 2 Step -1 If Range("Y" & i).Value < "" And Range("Y" & i) <= Date - 100 Then Range("Y" & i).EntireRow.Hidden = True End If Next i End Sub -- Breakfast Guy Regards, Breakfast Guy Forum Moderator www.thecodecage.com ------------------------------------------------------------------------ Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23938 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows based value of cell within row | Excel Discussion (Misc queries) | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding rows based on a value | Excel Discussion (Misc queries) | |||
Hiding Rows Based on Percentile | Excel Discussion (Misc queries) | |||
Hiding rows based on date | Excel Worksheet Functions |