Hiding Rows under the influence of multiple values.

[joavery]

Alright, I am sorta kinda new to VBA. Took a class in it 4 years ago, forgot everything. We did more button macros than passive macros.

Essentially I'm doing a money handling macro, and I need to hide certain rows (IE: the 29th - 31st for february, the 31st for April, June, September, November).

I can't figure out why, but when I use the hide row command repeatedly, once for each month, and which rows to remove, it doesn't work. If I use it once, and select February, as my value, the rows hide as I'd expect. But if I select another month, the rows don't show back up. Its boggling my mind. Any help would be greatly appreciated!

And, if this is not asking too much, I'm wondering if there is a way to negate those rows from calculations when they are hidden.. not sure if this is possible. But then again, this *is* VBA... goooo VBA!


Thanks y'all. Hopefully I can learn more VBA through use and contribute rather than beg weakly in my first post :P

[Jokacave]

Hi joavery,

Can you attach the file(s) to see what you want?

[joavery]

Sorry, it has been a busy month at work, and this wasn't my top priority at the moment.

I've attached the excel file I'm looking to fix below. The file's basic purpose is to tally day to day sales and calculate basic statistics. But, as we know, several months don't have 31 days, and I'd like to stop using three different iterations of the same file.

I'm trying to insert VBA code such that when a certain cell value is, say "February" then rows for dates 29 30 and 31 are removed, both visually and from calculations.

The problem I run into is I know how to make rows dissappear, but when I use this command multiple times in the same sub, it seemingly stops working. Moreover, I don't know how to remove them from calculations. (If possible I'd like to add a check box for "Leap Year" so that I can do 29 day February's as well in the same file.

Hope this makes sense.




31 DAY MTD tally.xls

[Jokacave]

Hi joavery,

Please, check the attached file

[joavery]

Interesting if not compoundly confusing.

While this solves my temporary needs, I will need to do much more research to understand the underlying commands. While the commands themselves are simple, I was not aware that they could be used in this fashion.

Clearly my one semester of "Engineering with Excel" in Civil Engineering was rather.. well.. BASIC basic.

In any event, I thank you deeply, for you've at least provided me with the "Correct" way from which to learn.

This is far more than I would have ever expected, I feel bad for having pro-bono work done on my behalf. I'm profoundly thankful!

[joavery]

Actually, it turns out my confusion may be founded in one respect. Was testing this out, cell A3 is currently displaying the following equation

=DAY(#NAME!EOMONTH(A2;0)) In order to find the last day of the month. Deleted the #Name! but its still not quite returning what I want, have been doing some google searches, but yielding little results, mostly VBA codes, and I know there's a function out there.

Edit: This may be due to using OpenOffice at home and Excel at work.

Edit 2: Found a working function for OpenOffice, not sure if this will work in Excel at work tomorrow. =DAYSINMONTH(A2) worked just fine as a substitute.

Edit 3: I'm running into the same problem I've been running into from the start. When I change months inside the program, it does NOT reflect the new changes. Entered January 2008, got 31 days. Entered February 2008, still had 31. Entered April, got 30, entered February, still had 30.

[Jokacave]

Hi again...

Please save the file in any place.
And change the formula to

Please Login or Register  to view this content.

and try again...