Hide specific scatterd columns

I would suggest you put a button on the spreasheet, and add this code to it.
The code will hide the columns you requested, print the workbook, and then unhide the columns again.
HTH
Cal

Sub HidePrint()
Dim ws As Worksheet
 
Set ws = ActiveSheet
ws.Range("F:G").EntireColumn.Hidden = True
ws.Range("J:M").EntireColumn.Hidden = True
ws.Range("T:U").EntireColumn.Hidden = True
ws.Range("X:Y").EntireColumn.Hidden = True
 
ws.PrintOut
 
ws.Range("F:G").EntireColumn.Hidden = False
ws.Range("J:M").EntireColumn.Hidden = False
ws.Range("T:U").EntireColumn.Hidden = False
ws.Range("X:Y").EntireColumn.Hidden = False
 
 
End Sub

Select allOpen in new window

Hi thanks is there a wat for me to define all the columns I wish to hide first? What about the printing letter and then legal code?

I missed the bit about the legal vs letter, but that's and easy fix.  As far as defining the columns you want to hide.  How would you like to do it?  If they are always the same, they are already hard-coded in the code, if they are going to change, then I need some type of ID on the workbook that would allow me to determine which ones to hide?
Cal

Sub HidePrint()
Dim ws As Worksheet
 
Set ws = ActiveSheet
ws.Range("F:G").EntireColumn.Hidden = True
ws.Range("J:M").EntireColumn.Hidden = True
ws.Range("T:U").EntireColumn.Hidden = True
ws.Range("X:Y").EntireColumn.Hidden = True
ws.PageSetup.PaperSize = xlPaperLetter
 
ws.PrintOut
 
ws.Range("F:G").EntireColumn.Hidden = False
ws.Range("J:M").EntireColumn.Hidden = False
ws.Range("T:U").EntireColumn.Hidden = False
ws.Range("X:Y").EntireColumn.Hidden = False
ws.PageSetup.PaperSize = xlPaperLegal
 
End Sub

Select allOpen in new window

Ok wow that is good code for printing, thanks
row 3 contains the headers so there is values in there.  but row 4 will not have any values in the columns I have identified.

I have been trying something like:
 Dim T_Hold_Col As Range
 Set T_Hide_Col = wb2.Range("J:M", "T:U", "X:Y")
    T_Hide_Col.EntireColumn.Hidden = True

It is not working of course, but it night give you an idea what I want to achive.
Thanks a bunch
Robert

OK, give this a try.

Sub HidePrint()
Dim ws As Worksheet
Dim cell As Range
 
Set ws = ActiveSheet
 
For Each cell In ws.Range("A3", ws.Range("IV3").End(xlToLeft))
    If cell.Offset(1, 0) = "" Then
        ws.Columns(cell.Column).EntireColumn.Hidden = True
    End If
Next cell
 
ws.PageSetup.PaperSize = xlPaperLetter
 
ws.PrintOut
 
For Each cell In ws.Range("A3", ws.Range("IV3").End(xlToLeft))
    If cell.Offset(1, 0) = "" Then
        ws.Columns(cell.Column).EntireColumn.Hidden = False
    End If
Next cell
 
ws.PageSetup.PaperSize = xlPaperLegal
 
End Sub

Select allOpen in new window

Hi Cbrine
Works like a charm. I wanted to work this into another vba macro I have but I am getting an error. Perhaps it is just my sequence of events . Can you have a quick look, I have remarked out the un hide and send as I want to hide then print. Your original design was outstanding but what am I missing here?

\Sub NBCN_Trades()
 
    Dim FileName As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim T_Buy, T_DTC, T_Date, S_Date, T_Shares, S_Name, S_Number, T_Comm, T_Price, T_Curr, T_Total, T_Clients, T_Alloc
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
   
    FileName = InputBox("Enter Original File Name")
   
    Set wb1 = Workbooks.Open("C:\Documents and Settings\rsernowski\Desktop\" & FileName & ".xls")
    Set wb2 = Workbooks.Open("C:\Documents and Settings\rsernowski\Application Data\Microsoft\Templates\NBCN Trade Advice.xlt")
    'copy required cells from original file'
    With wb1.Worksheets("Sheet1")
        T_Buy = .Cells(7, 3)
        T_DTC = .Cells(7, 4)
        T_Date = .Cells(7, 7)
        S_Date = .Cells(7, 8)
        T_Shares = .Cells(7, 9)
        S_Name = .Cells(7, 10)
        S_Number = .Cells(7, 11)
        T_Comm = .Cells(7, 13)
        T_Price = .Cells(7, 14)
        T_Curr = .Cells(7, 15)
        T_Total = .Cells(7, 16)
        End With
    wb1.Close
    'Open template and fill out required info'
    With wb2.Worksheets("Sheet1")
        .Cells(4, 5) = T_Buy
        .Cells(4, 8) = T_Date
        .Cells(4, 9) = S_Date
        .Cells(4, 14) = T_Curr
        .Cells(4, 15) = T_Comm
        .Cells(4, 16) = S_Name
        .Cells(4, 17) = S_Number
        .Cells(4, 18) = T_Shares
        .Cells(4, 19) = T_Price
        .Cells(4, 22) = T_Total
        .Cells(4, 23) = T_DTC
        End With
    'Determine USD and CAD and fill out resepective areas'
    If T_Curr = "CAD" Then
        Cells(4, 1) = "297Z01A"
        Cells(4, 3) = "29701BC"
        Cells(4, 4) = "TU"
    Else
        Cells(4, 1) = "297Z01B"
        Cells(4, 3) = "29701BD"
        Cells(4, 4) = "NU"
    End If
 ' Hide the un-needed columns and print the spreadsheet
 
   
    For Each cell In wb2.Range("A3", wb2.Range("IV3").End(xlToLeft))
        If cell.Offset(1, 0) = "" Then
        wb2.Columns(cell.Column).EntireColumn.Hidden = True
        End If
    Next cell
    wb2.PageSetup.PaperSize = xlPaperLetter
    wb2.PrintOut
   
   'Unhide the columns and email the file
   'For Each cell In wb2.Range("A3", wb2.Range("IV3").End(xlToLeft))
    'If cell.Offset(1, 0) = "" Then
     '   wb2.Columns(cell.Column).EntireColumn.Hidden = False
    'E'nd If
    'Next cell
   
    'send the document'
   
'    If MsgBox("Click OK to EMail, Cancel to STOP.", vbOKCancel) = vbCancel Then Exit Sub
 '   Set OutApp = CreateObject("Outlook.Application")
  '  OutApp.Session.Logon
   ' Set OutMail = OutApp.CreateItem(0)
    'With wb2
     '   With OutMail
      '      .To = "rsernowski@gmail.ccv"
       '     .CC = ""
        '    .BCC = ""
         '   .Subject = "Bluewater Trade Allocations"
          '  .Body = "Attached are Bluewater trade allocatons in excel"
           ' .Attachments.Add wb2.FullName
          '  .Send   'or use .Display
       ' End With
        'On Error GoTo 0
'       wb2.Close SaveChanges:=False
   ' End With
  End Sub