Solved

Hide specific scatterd columns

Posted on 2009-04-06
7
459 Views
Last Modified: 2012-05-06
Hello
I have spreadsheet that is being sent to two sources.  It is the same file with the same data but one source would like the spreadsheet without certain columns while the other source wants all the spreadsheet. So the spreadsheet will ALWAYS be 4 rows and 27 columns. I need to hide col F,G,J,K,L,M,T,U,X,Y , print the spreadsheet on landscape letter,print it in excel 2003, then unhide those columns, print on landscape legal, in excel 2007 format.  The template to start with is in excel 2007.  Oh yes those columns I wish to hide have NO data(an will never) in them while the other cols do have data.Help
Thanks
0
Comment
Question by:rsernowski
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
Comment Utility
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

Open in new window

0
 

Author Comment

by:rsernowski
Comment Utility
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?
0
 
LVL 16

Expert Comment

by:Calvin Brine
Comment Utility
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

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rsernowski
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Calvin Brine
Comment Utility
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

Open in new window

0
 

Author Comment

by:rsernowski
Comment Utility
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
0
 
LVL 16

Accepted Solution

by:
Calvin Brine earned 500 total points
Comment Utility
You are referencing the workbook object for my code.  It works on the Worksheet level. I've adjusted the code so that ws should now reference the active sheet in wb2.
HTH
Cal
 

Sub NBCN_Trades()

 

    Dim FileName As String

    Dim wb1 As Workbook, wb2 As Workbook

    Dim ws as worksheet

    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")

set ws = activesheet

    '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 ws.Range("A3", ws.Range("IV3").End(xlToLeft))

    'If cell.Offset(1, 0) = "" Then

     '   ws.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

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now