Hiding rows before printing
 

Hi
Hope somebody can help here.

We run a large drama group, lots of different classes etc. All info relating
to students is on a spreadsheet (Excel 2000) - names, addresses, phone
numbers etc. It also contains data on who has signed up to this terms class.
The class teachers get an attendance register printed from the spreadsheet.
I have a macro that hides those columns of data that the teacher does not
need to see. At the moment all students that were in a class last term are
printed out , even those who have not yet 're-enrolled' in the class.
How can I print only those rows where the the entry in the column for
enrolment status is showing 'Re-enrolled'?
Ideally I want to add to the print area macro I have written so this hiding
of unwanted rows happens automatically rather than have to manually hide
using filters or whatever. (As we have 29 classes and 500 students a manual
system is a bit onerous).

I did find a piece of VBA script that might have done the trick in a
discussion group - but now I can't find it!

Thanks in advance

"DaveM" wrote in message
...
Ideally I want to add to the print area macro I have written so this
hiding
of unwanted rows happens automatically rather than have to manually hide
using filters or whatever. (As we have 29 classes and 500 students a
manual
system is a bit onerous).

With those numbers, you could do with a routine that loops through a class
list. Everything that follows assumes you have labels in row 1.

Say your class list is in columns H and I (Class #/Teacher). With your
register table in columns A - D (Name/Class #/Present/Re-enrolled?),

Sub Print_Regs()
Range("H2").Select 'go to 1st # in Class # list

Do Until ActiveCell = ""

For Each Cell In Range("A2", Range("A65536").End(xlUp))
If Cell.Offset(0, 1) < ActiveCell Then 'check Class #
Cell.EntireRow.Hidden = True
ElseIf Cell.Offset(0, 3) < "Yes" Then 'check re-enrolled
Cell.EntireRow.Hidden = True
Else
Cell.EntireRow.Hidden = False
End If
Next Cell

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Cells.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select 'go to next # in Class # list
Loop

Cells.EntireRow.Hidden = False

End Sub

At the "SelectedSheets.PrintOut" stage you might want to work in other
stuff, eg: set header as Class #, set footer as date, etc.

HTH,
Andy

Hi Andy

Thanks for the response. I think I must explain that I am new to VBA €“ I do
have a VBA in Easy Steps book. But it is not well thumbed just yet.

I recreated your layout & copied your text into the VBA editor & then got
confused because it did not seem to do what I think you intended. Should I
have done something with the # symbols in the text?

If I explain a bit more about my set up maybe that would help. The columns
that I print in this particular report are A, B, C (which contains parent
name, & student name) J, K, N, X, Z, AA, & AB.

Classes are arranged in the same size blocks of rows (so there are always
blank rows in a lot of classes). Class 1 starts in row 18, finishes in row
51. Class 2 starts in 52, finishes in 85 etc.

What I need is a way of hiding rows that are not required before printing.
The print function is a manual response because not all classes are
necessarily going to be printed at the same time.

The enrolment status which qualifies whether a row should be printed or not
is in column Q. So this hiding part will have to be done before setting up
the print areas.

Hope that gives you more to work with.

Davem

"Andy Brown" wrote:

"DaveM" wrote in message
...
Ideally I want to add to the print area macro I have written so this
hiding
of unwanted rows happens automatically rather than have to manually hide
using filters or whatever. (As we have 29 classes and 500 students a
manual
system is a bit onerous).

With those numbers, you could do with a routine that loops through a class
list. Everything that follows assumes you have labels in row 1.

Say your class list is in columns H and I (Class #/Teacher). With your
register table in columns A - D (Name/Class #/Present/Re-enrolled?),

Sub Print_Regs()
Range("H2").Select 'go to 1st # in Class # list

Do Until ActiveCell = ""

For Each Cell In Range("A2", Range("A65536").End(xlUp))
If Cell.Offset(0, 1) < ActiveCell Then 'check Class #
Cell.EntireRow.Hidden = True
ElseIf Cell.Offset(0, 3) < "Yes" Then 'check re-enrolled
Cell.EntireRow.Hidden = True
Else
Cell.EntireRow.Hidden = False
End If
Next Cell

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Cells.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select 'go to next # in Class # list
Loop

Cells.EntireRow.Hidden = False

End Sub

At the "SelectedSheets.PrintOut" stage you might want to work in other
stuff, eg: set header as Class #, set footer as date, etc.

HTH,
Andy

One way - record a macro in which you apply an autofilter to show only the
Re-enrolled students, then incorporate that in your Print macro


"DaveM" wrote in message
...
Hi
Hope somebody can help here.

We run a large drama group, lots of different classes etc. All info
relating
to students is on a spreadsheet (Excel 2000) - names, addresses, phone
numbers etc. It also contains data on who has signed up to this terms
class.
The class teachers get an attendance register printed from the
spreadsheet.
I have a macro that hides those columns of data that the teacher does not
need to see. At the moment all students that were in a class last term are
printed out , even those who have not yet 're-enrolled' in the class.
How can I print only those rows where the the entry in the column for
enrolment status is showing 'Re-enrolled'?
Ideally I want to add to the print area macro I have written so this
hiding
of unwanted rows happens automatically rather than have to manually hide
using filters or whatever. (As we have 29 classes and 500 students a
manual
system is a bit onerous).

I did find a piece of VBA script that might have done the trick in a
discussion group - but now I can't find it!

Thanks in advance