Solved

Hide specific scatterd columns

Posted on 2009-04-06
7
466 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

628 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