• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Date and Unhide. Today () [SOLVED]

ianb

ianb

Member
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.
 
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
 
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.
 
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
 
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
 
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
 
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
 
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.
 
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.
 
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("A2").AutoFilter 1, ">=" & lDate, Operator:=xlAnd, Criteria2:="<" & 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...
 
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
 
@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!
 
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("A2").AutoFilter 1, ">=" & lDate, xlAnd, "<" & 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.
 
Hi, Smallman!


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


Examples of first line (without the spaces after < and before >):

< 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?
 
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.
 
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!
 
You must log in or register to reply here.
Back
Top