Need help with loops and arrays in VBA Macro

**lealea1982** · 08-24-2011, 10:35 AM

Hello,

I need a macro to run on the active sheet only. What it basically needs to do is find "01AStart" and select from that row through to the row containing "01AEnd" and then if there are no numeric values in that range it needs to hide it. Then it needs to move on to 01B, then 01C, etc etc. That is just for sheet 01. There are 30 sheets in total, named 01 through to 30, and there varying codes in each sheet (i.e. 01 goes up to 01K and 30 only has 30A).

Ideally I'd like one macro that can be used throughout the whole workbook (but would only actually run on the active sheet).

I think a loop array would work but I'm not quite sure how to write it. And I'd also need it to be a variable array rather than static (i.e. not error when it doesn't find 30B or something like that).

Hope that makes sense...

Thanks

**tigeravatar** · 08-24-2011, 10:53 AM

lealea1982,

Some clarification please. The 01A, 01B, etc. Are these all in the same column? Or is 01A in column A, 01B in column B, etc.?

~tigeravatar

**Mordred** · 08-24-2011, 10:54 AM

Hi lealea1982, It may be best for you and those that will help if you upload a mock workbook with mock data in it.

**lealea1982** · 08-24-2011, 11:04 AM

Sorry yes, they're all in Column A. I've attached an example of how it would look. The "Start" and "End" cells would actually have white font and be protected (so they're invisible) but it should give you an idea of what I mean. The first sheet shows how it would look before the macro, the second sheet shows how it would look after the macro.

Many thanks!

**tigeravatar** · 08-24-2011, 11:41 AM

lealea1982,

Assuming the sheets are named "01", "02", etc (no quotation marks) and nothing else, and the 01AEnd etc is correct (in the sample workbook, you had 01FStartEnd instead of 01FEnd, I changed it when testing the macro to just 01FEnd and it worked) you should be able to use the following:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

EDIT: Shortened code, updated code to work for more than single digit columns

**Kyle123** · 08-24-2011, 11:48 AM

Assuming the above you could also use - not quite as elegant as above

Please Login or Register  to view this content.

**lealea1982** · 08-24-2011, 12:06 PM

That works perfectly, exactly what I'm after... only one thing though, at the moment it only runs the loop up to 01F, is it possible to get it to keep going until it runs out of codes? For example, in my 05 page it starts at 05A and runs right through the alphabet all the way to 05AG!

Thanks again for the help, it's so appreciated!!

**Kyle123** · 08-24-2011, 12:07 PM

have a whirl with mine *should* work for any amount of data

**lealea1982** · 08-24-2011, 12:28 PM

Absolutely perfect!! Thank you so much!!

**lealea1982** · 08-25-2011, 11:16 AM

Quick addition to this one if that's ok...

On a couple of my pages the tables only contain formulas, and no hard values. This macro doesn't seem to work on any tables that contain formulas only. Do you know how I can adjust it so that it'll pick up on that?

Alternatively, could the macro be amended to only hide the cells where the SUM = 0?

Many thanks again!!

**Kyle123** · 08-25-2011, 11:31 AM

Have a try with this: - haven't tested it though

Edit Actually just tried the original code on tables with only formulas and it worked :S
Could you re-post your workbook with the tables that don't work?

Please Login or Register  to view this content.

**lealea1982** · 08-25-2011, 12:07 PM

That one hides all of them unfortunately (both formulas and values).

Is there a way to make it hide those where the SUM of the highlighted range is 0? That might make it work a bit better for me.

Thanks again!

**lealea1982** · 08-25-2011, 12:33 PM

Here it is. I've highlighted the cell that has just a formula in it in yellow - that macro seems to hide that table though...

**Kyle123** · 08-25-2011, 02:50 PM

This works

Probably a neater way of writing it though

Please Login or Register  to view this content.

**snb** · 08-25-2011, 03:22 PM

Maybe:

Please Login or Register  to view this content.

**Kyle123** · 08-25-2011, 03:36 PM

Hehehe brilliant, as I said - always a neater way

**lealea1982** · 08-26-2011, 05:18 AM

Originally Posted by Kyle123

This works

Probably a neater way of writing it though

Please Login or Register  to view this content.

This one worked perfectly (the second one wouldn't hide anything, I think possibly because the totals have complex formulas in them (more complex than the example workbook I attached before).

The only problem now is if it picks up a formula within the table it doesn't hide it, even if the result of the formula is zero (2 of my pages have tables that are filled with formulas - many of which may be 0). Is it better to do a macro that picks up whether the SUM of the tables is zero for those 2 pages?

Thanks for all your genius help guys!

**Kyle123** · 08-26-2011, 05:38 AM

try:

Please Login or Register  to view this content.

**snb** · 08-26-2011, 06:09 AM

@Kyle

Please Login or Register  to view this content.

**lealea1982** · 08-26-2011, 06:56 AM

I can't get either to work unfortunately! When I use the first code it hides everything, when I insert the second code it doesn't hide anything!
Might have to admit defeat with this one!

Scrap that - it's working!! Hurrah!! All I did was close it all down and open it again in frustration.

Thank you so much for your help, you've made my day!!

**Kyle123** · 08-26-2011, 07:54 AM

@snb won't that won't that always return true? On error resume next will skip to the 'then' Part so we need an error catch

It does reduce code though, I've amended based on your idea:

Please Login or Register  to view this content.

lealea1982, I have checked this and it works in the attached - I have a formula that results in 0 in one of the tables - the code hides this table

**snb** · 08-26-2011, 08:15 AM

@Kyle

Did you test the code ?

Please Login or Register  to view this content.

**Kyle123** · 08-26-2011, 08:25 AM

@snb

Yes, try the following as per your original suggestion, in an If statement, the code fails so resumes next:

Please Login or Register  to view this content.

**snb** · 08-26-2011, 08:51 AM

@kyle,

Use

Please Login or Register  to view this content.

Need help with loops and arrays in VBA Macro

LinkBack

Thread Tools

Rate This Thread

Display

Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Re: Need help with loops and arrays in VBA Macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions