Don't print if Col M = 'N' - Macro help!

[eddy]

Hi with thanks to TSTOM I have the following macro but I just need to get it to one more thing.
It produces a price list from data taken from the'MAIN' worksheet.
Col M either contains a 'Y' or an 'N' . If its 'N' then I dont want that line item to be printed.
All help appreciated.
eD


Option Explicit

Sub PDF_Export()
Dim HeaderText(), HeaderRow(), RowCntr As Long, NewElement As Integer, sp As Worksheet
RowCntr = 18
Windows("MasterQuoteSheet.xls").Activate
With Sheets("Main")
ReDim HeaderText(1 To 1): ReDim HeaderRow(1 To 1)
HeaderText(1) = .Cells(RowCntr, 12).Value
HeaderRow(1) = RowCntr
Do Until .Cells(RowCntr, 12) = ""
If .Cells(RowCntr, 12) <> HeaderText(UBound(HeaderText)) Then
NewElement = UBound(HeaderText) + 1
ReDim Preserve HeaderText(1 To NewElement)
ReDim Preserve HeaderRow(1 To NewElement)
HeaderText(NewElement) = .Cells(RowCntr, 12)
HeaderRow(NewElement) = RowCntr
End If
RowCntr = RowCntr + 1
Loop
NewElement = UBound(HeaderText) + 1
ReDim Preserve HeaderRow(1 To NewElement)
HeaderRow(NewElement) = RowCntr
Windows("MasterQuoteSheetUtils.xls").Activate
Set sp = Sheets("PriceList")
With sp.Range("C48:F5000")
.ClearContents
Application.ScreenUpdating = False
.Font.Bold = False
.Rows.RowHeight = 15.75
.Font.Name = "Arial"
.Font.Size = 9
End With
For NewElement = 1 To UBound(HeaderText)
For RowCntr = HeaderRow(NewElement) To HeaderRow(NewElement + 1) - 1

sp.Cells(RowCntr + 30, 3) = .Cells(RowCntr, 2)
sp.Cells(RowCntr + 30, 5) = .Cells(RowCntr, 3)
sp.Cells(RowCntr + 30, 6) = .Cells(RowCntr, 6)

Next
Next
RowCntr = 0
For NewElement = 1 To UBound(HeaderText)

RowCntr = RowCntr + 1
Rows(HeaderRow(NewElement) + 30).Insert Shift:=xlDown
sp.Cells(HeaderRow(NewElement) + 30, 5) = " " & HeaderText(NewElement)
sp.Cells(HeaderRow(NewElement) + 30, 5).Font.Bold = True
sp.Cells(HeaderRow(NewElement) + 30, 5).Font.Size = 12
HeaderRow(NewElement + 1) = HeaderRow(NewElement + 1) + RowCntr

Next
End With
Application.ScreenUpdating = True
Set sp = Nothing
End Sub


[ This Message was edited by: eddy on 2002-07-12 23:40 ]

[Tom Schreiner]

I'm a slacker.

Replace your procedure with this one.
Sorry Ted.

Sub PDF_Export()

Dim HeaderText(), HeaderRow(), RowCntr As Long, NewElement As Integer, sp As Worksheet, c As Range

RowCntr = 18

Windows("MasterQuoteSheet.xls").Activate

With Sheets("Main")

    ReDim HeaderText(1 To 1): ReDim HeaderRow(1 To 1)

    HeaderText(1) = .Cells(RowCntr, 12).Value

    HeaderRow(1) = RowCntr

    Do Until .Cells(RowCntr, 12) = ""

        If .Cells(RowCntr, 12) <> HeaderText(UBound(HeaderText)) Then

            NewElement = UBound(HeaderText) + 1

            ReDim Preserve HeaderText(1 To NewElement)

            ReDim Preserve HeaderRow(1 To NewElement)

            HeaderText(NewElement) = .Cells(RowCntr, 12)

            HeaderRow(NewElement) = RowCntr

        End If

        RowCntr = RowCntr + 1

    Loop

    NewElement = UBound(HeaderText) + 1

    ReDim Preserve HeaderRow(1 To NewElement)

    HeaderRow(NewElement) = RowCntr

    Windows("MasterQuoteSheetUtils.xls").Activate

    Set sp = Sheets("PriceList")

    sp.Range("J48:J5000").ClearContents

    With sp.Range("C48:F5000")

        .ClearContents

        Application.ScreenUpdating = False

        .Font.Bold = False

        .Rows.RowHeight = 15.75

        .Font.Name = "Arial"

        .Font.Size = 9

    End With

    For NewElement = 1 To UBound(HeaderText)

        For RowCntr = HeaderRow(NewElement) To HeaderRow(NewElement + 1) - 1

            

            sp.Cells(RowCntr + 30, 3) = .Cells(RowCntr, 2)

            sp.Cells(RowCntr + 30, 5) = .Cells(RowCntr, 3)

            sp.Cells(RowCntr + 30, 6) = .Cells(RowCntr, 6)

            sp.Cells(RowCntr + 30, 10) = .Cells(RowCntr, 13)

        Next

    Next

    RowCntr = 0

    For NewElement = 1 To UBound(HeaderText)

        

        RowCntr = RowCntr + 1

        Rows(HeaderRow(NewElement) + 30).Insert Shift:=xlDown

        sp.Cells(HeaderRow(NewElement) + 30, 5) = "    " & HeaderText(NewElement)

        sp.Cells(HeaderRow(NewElement) + 30, 5).Font.Bold = True

        sp.Cells(HeaderRow(NewElement) + 30, 5).Font.Size = 12

        HeaderRow(NewElement + 1) = HeaderRow(NewElement + 1) + RowCntr

        

    Next

End With

For Each c In sp.Range("J48:J5000")

    If c = "N" Then

        Rows(c.Row).EntireRow.Hidden = True

    Else

        Rows(c.Row).EntireRow.Hidden = False

    End If

Next

Application.ScreenUpdating = True

Set sp = Nothing

End Sub

Simply hides the rows which have N's
Excel will not print hidden rows.
Cheers from the slacker across the pond!
Tom

[eddy]

No way!
Great solution as usual, many thanks Tom.
Ted