auto hide data

  • Thread starter Thread starter aditya
  • Start date Start date
A

aditya

I want to do any of following thing

(1) On day2 when I open excel sheet, data that i entered on day1 in
perticular sheet (say sheet1) should be hidden automatically.
Or
(2) On day2, when I open excel sheet & enter today's date on sheet1,
data entered on day1 should go hidden.

i want to do this daily basis.
Pls also tell me how to unhide this.

thanks an advance
 
Possibly, all you need to do is Data filter but you may need to use VBA
programming language.

Data filter:
XL2003
http://office.microsoft.com/en-us/excel/HP051998001033.aspx?pid=CH062527971033

XL2007
http://office.microsoft.com/en-us/excel/HP100739411033.aspx?pid=CH100648451033

You filter would be:
- select (Custom...)
- change the date drop-down to 'is less than'
- write the day's date



A VBA solution should be relatively easy for someone familiar with VBA
however, you will need to give some idea of data layout and what you wish to
see each day. For example, you talk about day 1 data being hidden on day 2.
What happens on day 3? Unhide day 2? On day 213? In other words, are you
really looking to hide all previous date's data? A data layout would be
useful. Example:

Date, Product, Qty, Price, Total



The macro to hide data laid out this way would be

Sub HideData()
ActiveSheet.AutoFilterMode = False
Range("A2").Select ' a cell below your headings
Selection.AutoFilter Field:=1, Criteria1:=">=" & --Format(Date, "0"),
Operator:=xlAnd

'Field:= 1' would be the date field if in column A
'change to Field:= 2 if date in column B, 3 if Column C, etc.

End Sub


To use the above:
-right-click the tab or the relevant worksheet
- copy and paste the code
 
Back
Top