macro to hide rows based on formula?

  • Thread starter Thread starter Scott T. Lindner
  • Start date Start date
S

Scott T. Lindner

I have to apologize for my ignorance in advance. I know nothing of VB or
embedding macros in Excel. I can learn quickly though. Before I get to the
real question, does anyone have a suggestion for a website that can hold my
hand through the basics of using VB macros in Excel starting from the "I'm
an idiot" stage?

I want a macro that I can electively run on a single sheet that will hide
all rows based on a formula I define. I'd like it to operate based on a
button on the sheet or even just by entering the sheet. Either is fine. I
know this is actually two or three questions in one:
1) how to initiate the macro by entering the sheet or pressing a button
2) how to hide specific rows in a macro
3) how to use a formula in a macro (maybe not really an issue)

Thanks all.
Scott
 
My suggestion is to record a macro then step through the
macro (by hitting the F8 key) and watch what each line in
the macro does. To see the code click tools > macro >
select the macro then click the edit button. To run your
maco I also suggest you click tools > macro > options to
assign a "ctrl character" to run the macro insted of a
button. For a great web site on Excel macros go to
http://www.mvps.org/dmcritchie/excel/excel.htm.

Marty
 
I don't have the formula written in Excel terms just yet so I'll describe
it.

I have a bunch of items listed on one sheet. There's many columns of data
for each item. I want to reformat and copy those items from the first sheet
and put them on the second sheet but the most important part is that I want
to hide certain items as a way of filtering what is displayed. I definitely
want the rows hidden but still there though. On yet another sheet I'm
collecting information that affects the filtering used for hiding or showing
the rows.

Does that make sense or am I too vague and need to get explicit to the exact
details?

Scott
 
Well, Excel has a built in ability to filter, even to another location -
Data=>Filter=>Advanced filter.

If your criteria is very complex on deciding which rows to hide, you can put
formulas on the end of the rows to make the determination.

Almost anything you can do manually can be done using code. One way to get
the code is to turn on the macro recorder (tools=>Macro=>Record a new
macro), then perform the actions manually.

This give crappy code, but shows you the syntax of the major command that
you need to use. It is very effective if you are using major Excel
capabilities like Advanced filter or Subtotal.

I know I didn't use any functions in the Data menu (except sort), so I don't
know if you are familiar with these or not, but they may minimize the amount
of code you need to do.

As far as too vague. Yes, unfortunately, you have to tell the computer in
minute detail what you want it to do - you can't point in a general
direction and and say put it there.
 
I'm an enginerd so I'm sure I can figure it out. Been writing software for
nearly 20 years so I'm sure I can handle it. The deal is that I've never
even attempted to learn VB. I don't necessarily want to pick it up because
I doubt I'll use it enough to justify the time learning it to any great
degree. I think your comment to use the recorder is the best approach.
It'll give me the pieces of code I need so I can clean it up and make it do
exactly what I want. I hate to say it but I hadn't thought of that simple
idea. Doh. :) Thanks!

Cheers
Scott
 
Back
Top