Solved

Hide specific scatterd columns

Posted on 2009-04-06
7
464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24079351
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
ID: 24080184
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
ID: 24080825
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rsernowski
ID: 24081195
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
ID: 24081265
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
ID: 24086172
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
ID: 24086757
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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