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.

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.

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.

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.

Originally Posted by wallyeye

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.

Just change the following line

Please Login or Register  to view this content.

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?

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.

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.