This macro does what I explained in the earlier post:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim LastRow As Long
Dim LastHeaderRow As Long

Set wks = Worksheets("sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastHeaderRow = 3
FirstCol = 2
LastCol = .Cells(LastHeaderRow, .Columns.Count).End(xlToLeft).Column

'remove any filter
.AutoFilterMode = False

'apply it to where we want it
.Range(.Cells(LastHeaderRow, "A"), .Cells(LastRow, LastCol)).AutoFilter

For iCol = FirstCol To LastCol
'hide all the columns from B over to the right
.Range(.Cells(1, FirstCol), .Cells(1, LastCol)) _
.EntireColumn.Hidden = True
'unhide the column that we're printing (along with A)
.Columns(iCol).Hidden = False

'show all the data
If .FilterMode Then
.ShowAllData
End If

'show only non-blank rows in that column
.AutoFilter.Range.AutoFilter field:=iCol, Criteria1:="<"

'print it out
.PrintOut preview:=True
Next iCol
'remove the filter
.AutoFilterMode = False
'show all the columns
.UsedRange.Columns.Hidden = False
End With
End Sub

It assumes that you can find the range to filter based on the lastheaderrow (row
3 has to have something in it for the last column).

And it figures that column A defines how many rows should be looked at.

Change the preview:=true to :=false after you've tested it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Rich wrote:

The issue is I'm trying to do this all in One Worksheet and Print Multiple
reports using
a Macro.

Example:
COL A COL B COL C COL D COL E

John Doe 1.00 5.00
Jane Doe 2.00 6.00
Tom 8.00
Dick 1.00
Harry 2.00 10.00

When the Reports Print, I need the following resluts,
One Report for COL B, But I should only see John Doe and Dick
A report for COL C and Only Jand Doe and Harry should be listed, and A report
for Col D that Johh, Jand and Tom should be Listed. And Finally the Rport
for E should only have Harry.

Is there anyway in the Macro that I can put conditions to only print the
Employees that have data in the col that I'm Printing.

Rich

"Dave Peterson" wrote:

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson


--

Dave Peterson