VBA Date and Unhide. Today () [SOLVED]

ianb · Jul 22, 2013

Hi,

All my data is showing from 2011 to 2014 for each day.

I would like to show only 3 months data.

I can hide all from 2011, 2012 and 2013 to end of April.

If I hide today () ownards can I unhide if today before adding the data.

In addition is it possiable to have hiding of over 3 months old.

today () A1

Data starts with date @ A5-A450.......

Many Thanks.

Smallman · Jul 22, 2013

Hi Ian

You said you have data from 2011 for each day. Is that each physical day? If I put 1 Jan 11 in Cell A5, cell A6 Has A5 + 1 and drag this down to get to today I reach Row 938. For test purposes this is what I have done. Now in A1 I put the formula = Today()

Now the part which will dynamically hide your rows. It is not very efficient to iterate through the range so I would use a hidden filter. It will hide all of the data that is more than 91 days old.

Here is what the procedure would look like. Put it in a regular module. If it is not what you are after post you file and someone will have a look at it.

Take care

Smallman

Sub FilteroutData()

Dim lDate As Long

lDate = [A1] - 90

Range("A1").AutoFilter 1, ">=" & lDate, , , 0

End Sub

ianb · Jul 22, 2013

I am getting a mismatch. runtime error 13.

My dates are monday to friday as I add sat and sun to the friday figure.

Also they are in this format 07/07/2011 as a date.

A1 = today ()

A5 is the start fo the filter names date

a6 = 01/07/2011

A502 = 20/06/2013

Sub FilteroutData()

Sheets("Statistics Daily").Select

Dim lDate As Long

lDate = [A1] - 90

Range("A1").AutoFilter 1, ">=" & lDate, , , 0

End Sub

Can you advise please.

Also I can build this program into my update program when the data is collected each day hence I will also have 90 days showing.

Thansk again, I think I am going in the right direction.

ianb · Jul 22, 2013

Sub FilteroutData()

Sheets("Statistics Daily").Select

Dim iDate As Date

iDate = [A3] ' A2-90

Range("A5:A999").AutoFilter 1, ">=" & iDate, , 0

End Sub

This hides all the dates

Smallman · Jul 22, 2013

Hi Ian

In VB there is very little need to ever select anything so;

sheets("Statistics Daily").select

Is not required for this or just about any other line of code. It is curious that this procedure does not work at your end. It works fine at my end I have A1 and cells from A5 going south, formatted at you have described above.

It is a shame this website does not have an upload facility as I normally upload a file with every post I put together (in another forum). So I could show you that this method runs very smoothly.

Mmmm I just had a thought. I put the file here;

http://www.ozgrid.com/forum/showthread.php?t=180936&p=675718#post675718

It is a dedicated test area online and should allow you to view the file.

Take care

Smallman

ianb · Jul 22, 2013

Getting closer...

Sub FilteroutData()

Sheets("Statistics Daily").Select

Dim iDate As Date

Dim iDate2 As Date

iDate = [A2] ' today ()

iDate2 = [A3] ' A2-90

Range("A5").AutoFilter 1, ">=" & iDate

'Range("A5").AutoFilter 1, "<=" & iDate2

End Sub

Smallman · Jul 22, 2013

Hi Ian

Download the file and you are there. Or follow the instructions from post 2 and you are 'winning'. The code is all laid out for you and it does work nicely.

Take care

Smallman

ianb · Jul 22, 2013

Thanks. You have your working therefore I will test with yours and transfer over the construction. will keep you informed as to why it did not work.

Many thanks for your time. Ian.

ianb · Jul 22, 2013

This works for hiding before -90 days.

Option Explicit

Sub FilteroutData()

Dim lDate As Long

lDate = [A2] - 90

Range("A2").AutoFilter 1, ">=" & lDate

End Sub

Do you know how I add to show up to today then all the dates with formulas will be hidden after today giving the charts a good look. Many thanks. Its improving.

ianb · Jul 22, 2013

And is before today ()

ianb · Jul 22, 2013

Hi Smallman,

This has worked for me.

[pre]

Code:

Option Explicit

Sub FilteroutData()
Dim lDate As Long
lDate = [A2] - 90
Dim lDate2 As Long
lDate2 = [A2] + 1
Range(&#34;A2&#34;).AutoFilter 1, &#34;&#62;=&#34; & lDate, Operator:=xlAnd, Criteria2:=&#34;&#60;&#34; & lDate2
End Sub

[/pre]
YOur idea and a little recording from the macro.

Many thanks. now I need not change it every day.

Thanka again...

Smallman · Jul 22, 2013

Hi Ian

I got hit by the sand man last night so could not see your problem through. This is good. All I can add is a style guide, move your second variable under your first and your last line could be optimised a little.

' Range("A2").AutoFilter 1, ">=" & lDate, xlAnd, "<" & lDate2

Now I have a question for you. How do you get the code to indent on this forum? I have been playing around with the ' symbol but it does not have the right look just yet. I am new to this forum.

Thanks

Smallman

SirJB7 · Jul 23, 2013

@Smallman

Hi!

Check at the text below the "Tags (comma sepereted)" text box (but read "separated", please, the KeyMaster wrote that line after a six-pack of cheap beer on a fasting day), just to the left of the "Send Post" button. You'll find this:

Allowed markup: a blockquote code em strong ul ol li.

You can also put code in between backtick ( ` ) characters.

(That 1st line always sounded to me as Swahili or Martian)

So to use indent and don't let indent play on you do this:

1) Type 2 consecutive backticks (or French grave accent, Ascii 96)

2) Paste the code between them

3) Be aware that the 1st line of code starts at column 1, otherwise it won't get indented: if you find out how to get it damn indented without starting at col 1, I'm not sure I'm prepared to receive such notice...

Regards!

Smallman · Jul 23, 2013

Hi SirJB7

LOL - ha. Thanks for your reply. I was scratching my head over the past couple of days trying to work this out and that line ul ol li. Is that just one of this websites idiosyncrasies and is getting left there into perpetuity? Or can the administrators change it so the typo for Separated and some of the cryptic stuff can get cleaned up? While I am at it there in the last day I have seen 3 spam posts. Is there a way to report these posts. They do disappear reasonably quickly (about an hour on average) but are annoying. I am going to use the following as a bit of a test environment. So here is the code from above.

[pre]

Code:

Sub FilteroutData()
Dim lDate As Long
Dim lDate2 As Long
lDate = [A2] - 90
lDate2 = [A2] + 1
Range(&#34;A2&#34;).AutoFilter 1, &#34;&#62;=&#34; & lDate, xlAnd, &#34;&#60;&#34; & lDate2
End Sub

[/pre]
Not sure where it will appear. Thanks so much for your guidance. Really appreciate your time and assistance.

Take care

Smallman

Edit - Nope I must have done something wrong. Be assured this is user error not your instructions. Thanks again.

SirJB7 · Jul 23, 2013

Hi, Smallman!

1st backtick ok, last backtick (´) wrong, should be like 1st one.

Examples of first line (without the spaces after < and before &#62

:

< em >This is using em< /em >: This is using em

< strong >This is using strong< /strong >: This is using strong

< ul >This is using ul< /ul >:

This is using ul

< ol >This is using ol< /ol >:

[list type=decimal]This is using ol[/list type=decimal]

< li >This is using li< /li >:

[*]This is using li

Regards!

PS: Ha ha ha... I only used strong and em... the others are weird and annoying, do you see where li ends?

Smallman · Jul 23, 2013

mmmm

Lets give this another try as I have butchered a few posts since your excellent instructions went over the top of my head. Crossing my fingers.

'Sub FilteroutData()

Dim lDate As Long

Dim lDate2 As Long

lDate = [A2] - 90

lDate2 = [A2] + 1

Range("A2").AutoFilter 1, lDate, xlAnd, lDate2

End Sub'

Thanks for your patience.

Smallman

Edit - it is not there yet but I am getting closer. I have colour - we have colour!!!!! I am sure indents must be in the EM and Strong part of your post. I will get there. Seems a lot of faf to make my code look pretty!!! Oh well.... sigh.

SirJB7 · Jul 23, 2013

HI, Smallman!

You've reduced your post font but not the other posts! That's new... maybe it's a feature only available for "Small"xxx nicks!

https://dl.dropboxusercontent.com/u/60558749/VBA%20Date%20and%20Unhide.%20Today%20%28%29%20%5BSOLVED%5D%20%28for%20Smallman%20at%20chandoo.org%29.png

This time you used ' instead of grave accent ` U+0060 from the Character Map at Start, Accessories, System Tools.

Regards!

VBA Date and Unhide. Today () [SOLVED]

ianb

Member

Smallman

Excel Ninja

ianb

Member

ianb

Member

Smallman

Excel Ninja

ianb

Member

Smallman

Excel Ninja

ianb

Member

ianb

Member

ianb

Member

ianb

Member

Smallman

Excel Ninja

SirJB7

Excel Rōnin

Smallman

Excel Ninja

SirJB7

Excel Rōnin

Smallman

Excel Ninja

SirJB7

Excel Rōnin