Best Way

[Guest]

I am an English Teacher at a Gates Foundation High School.

I have never written a Macro Before and I need an opinion. The WooKBook
that we have set up is the largest I have ever seen.

I want to reduce it in size and cut the columns down for just just what we
need in English.

We have 51 columns that pertain to different English Classes. I just want
the ones that pertain to what I teach, but the math formula for that is very
complex.

I also want to remove the students that are not eligible for certain classes.

Any recommendations as to the best way to do this?

Thank You Very Much

Mrs X.

 

[Dave Peterson]

I'd keep all the data where it is.

But learn how to hide the columns you don't want. You could do this in a macro
in a different workbook if you can't update that original workbook.

Then learn about Data|Filter|autofilter.

You'll be able to hide rows you don't want to see. Here's hoping that you have
a field that is used to indicate whether the students are eligible.

By keeping the data in one location, you'll always know that you're working with
accurate data. A big problem if you create your own copy of the data.

 

[Guest]

Why not just copy the workbook. Select the entire columns you want to delete
and do Edit=>Delete. You can select entire columns by clicking at the top of
the column. You can use the control key with the mouse to select
non-contiguous columns.

After you get down to the columns you want, then start working on the rows
in a similar fashion.

 

[Guest]

Tom;

I am sure if I ever get this to work (our programming staff does not
want to mess with it) that the rest of the teachers will want to use it. I
want to keep it as easy as possible.

The WorkBook is so large it took me between 1.5 and 2.0 hours just
for my 4th period class.

Is there a book you can recommend before I take this on?

Thanks

Mrs X

 

[Guest]

Dave;
If I hide a column can I still look in that column with a Macro.
What a student does in certain classes also determines what tests the student
should take and if the student should be in Literature or Advanced
Composition as an example. I want to plan my attack before I try to start
writing code, script, etc.

Thanks Again

Mrs X

 

[Dave Peterson]

Yes. You can look at cells that are in columns that are hidden or in cells that
in rows that are hidden.

You can also refer to those hidden cells in formulas in the worksheet, too.

And if you need to look at those cells in your code, make sure you do all your
processing before you delete any rows or columns (if you decide you want to
delete stuff).

 

[Guest]

Dave

Thank You, I am starting a collection of notes.

Nancy X.

 

[Guest]

Based on your statement "never written a macro before" I would recommend ISBN
0-7645-3758-X "Excel 2003 All-In-One Desk Reference for Dummies" by Greg
Harvey. Make sure you get that one - there's another by same author, similar
title but with less info in it. This one is a "9 books in 1" deal and
besides all the basics of Excel, it has a section specifically about Excel
and VBA.

Another that may be useful later as you get more familiar with macros and
VBA in general may be ISBN 0-7645-3263-4: "Excel 2000 Power Programming with
VBA" by John Walkenbach. There's probably a 2003 version out, but I have the
2000 here and it still serves the need.

Once you become familiar with setting up variables to reference the objects
(worksheets, cells, etc) in Excel you will find that you can often figure out
how to get a job done in code by first recording a macro to do that type of
operation so that you can see the Excel objects referenced and the properties
and methods used to get the job done in a fixed manner. Then you can modify
the recorded macro to add looping and change fixed references to ones that
will change as needed to get the 'custom' macro needed to meet your
requirement.