+ Reply to Thread
Results 1 to 12 of 12

Hide empty columns that appear the same from multiple worksheets

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Cool Hide empty columns that appear the same from multiple worksheets

    Hi there,

    I would like to hide the empty columns if they are appear the same in all 5 work sheets. The check for empty columns is from row 8 to row 47. The problem I have is I can select 5 fixed sheets. I would like to see if I can select as many sheets that I want based on user input. Below is the code:

    Thank you for helping me.

    Please Login or Register  to view this content.
    Last edited by Cutter; 09-21-2012 at 09:53 PM. Reason: Replaced QUOTE tags with CODE tags

  2. #2
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    Is there any better loop for this code?
    While Sheet1.Cells(r, iCol).Value = 0 And Sheet2.Cells(r, iCol).Value = 0 And Sheet3.Cells(r, iCol).Value = 0 And _
    Sheet4.Cells(r, iCol).Value = 0 And Sheet5.Cells(r, iCol).Value = 0

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    I am sorry. But is it difficult to solve? Please help

  4. #4
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Hide empty columns that appear the same from multiple worksheets

    Here is some fairly efficient code:

    Please Login or Register  to view this content.
    You will need to set the range to match yours, if it changes. How do you want the user to select the sheets to compare? You could have them in a range, and just assign arrSheets to the range, tweaking the code to reflect two dimensions in the array.

  5. #5
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Hide empty columns that appear the same from multiple worksheets

    You could use an input box to ask user up to what sheet to process then basically if sum of column range is 0 in all selected sheets then hide the column. If I understand you correctly based on your code you could use
    Please Login or Register  to view this content.
    You may need to modify to your needs.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    Thank you, Anthony for your time. I have a question for you:
    The input from user is to get shtNo. So the sheets will be selected from left to right starting from first sheet to shtNo sheet, right? Can I specify the name of the sheet, so that no matter where they are in the workbook, they will be selected. For example, the sheets' names are cals1, cals2, cals3,..., calsn. The name Cals is unchanged, just the index number.

    Again thank you.
    Last edited by tinhtu; 09-22-2012 at 12:24 AM.

  7. #7
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    Hi Wallyeye
    Thank you so much for your time. The sheets' names are cals1, cals2, cals3,..., calsn. The name Cals is unchanged, just the index number. User will input the n. Then the sub should look for sheets cals1 to calsn to check for empty columns. How can I assign them in a range? Forgive me for my stupid question.

    Quote Originally Posted by wallyeye View Post
    Here is some fairly efficient code:

    Please Login or Register  to view this content.
    You will need to set the range to match yours, if it changes. How do you want the user to select the sheets to compare? You could have them in a range, and just assign arrSheets to the range, tweaking the code to reflect two dimensions in the array.
    Last edited by tinhtu; 09-22-2012 at 12:31 AM.

  8. #8
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Hide empty columns that appear the same from multiple worksheets

    Just change the following line
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    I did try your suggestion. However, It will not pickup the Cals2 sheet if it is in 3rd sheet or so on. I input shtNo= 2. What do you recommend?
    Last edited by tinhtu; 09-22-2012 at 01:25 AM.

  10. #10
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Wink Re: Hide empty columns that appear the same from multiple worksheets

    Hi Anthony,

    I replaced Worksheets(x).Columns(iCol).EntireColumn.Hidden with Worksheets("cals" & CStr(x)).Columns(iCol).EntireColumn.Hidden.. Then it works. I am so excited. Thank you

    Below is the worked sub:

    Please Login or Register  to view this content.
    Last edited by tinhtu; 09-22-2012 at 02:45 AM.

  11. #11
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Hide empty columns that appear the same from multiple worksheets

    Well done - sorry - missed last part that actually hides the columns. If your problem is now solved could you please mark the thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. It would be much appreciated.

  12. #12
    Registered User
    Join Date
    08-22-2012
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Hide empty columns that appear the same from multiple worksheets

    Thank you Anthony. I did mark the thread solved. I still work on the last part. I want to sum all all worksheet at row 49 and put the results at row 50 on the last sheet. Any suggestion? If I cannot do it I will open the thread as unsolved again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1