Jtdylktuy

I inherited an Excel workbook from a colleague which left the company. Somehow, this colleague managed to hide or delete all columns to the right of a sheet. For instance, currently this worksheet ends at column BG -- it's not that column BH is empty, it is not shown at all. Whether it has been hidden or deleted, I have no idea. Said colleague can't be reached to ask how this was done, and even if reached, probably wouldn't remember anyway.

Periodically I must add two columns to the right of this sheet, copying these columns from the last two, so that the cells referenced by the formulas are also shifted. For instance, suppose I currently have some in BF6 a formula like "=AnotherSheet!C6", and in BG6 a formula like "=AnotherSheet!D6", where AnotherSheet is some other sheet in my workbook. What I need to do is create columns BH and BI and populate them with a copy of the contents of BF and BG, respectively. Returning to the example, BH6 should contain the formula "=AnotherSheet!E6" and BI6 should contain the formula "=AnotherSheet!F6".

Now, if BH and BI weren't hidden, or deleted, or whatever, I could just select BF and BG, then copy and paste it into BH and BI. Formulas would be shifted exactly in the way I need them. Since I can't do that, I have to insert new columns. However, columns are always inserted to the left of the current selection (I haven't found a way of inserting them to the right). Hence I end up with "=AnotherSheet!A6" in BF6, "=AnotherSheet!B6" in BG6, "=AnotherSheet!C6" in BH6 and "=AnotherSheet!D6"; i.e. the original contents of BF6 and BG6 are carried over without change to BH6 and BI6, and since new cells are inserted to the left, formula contents are shifted left as well.

Can anyone suggest a way to either:

• recreate or unhide all columns to the right that were deleted or hidden?


• insert columns to the right of the current selection?


• some other way to quickly create 2 columns to the right that are a copy (with formulas correspondingly shifted) of the current 2 columns?

If it's not asking too much, no VBA solutions please. And in case it makes a difference, this is on MS Excel 2011 for Mac.

Here is a solution I came up with, but I am certain there must be a better solution.

Starting with a sheet ending at row BG, such that BF6 contains the formula "=AnotherSheet!C6" and BG6 contains the formula "=AnotherSheet!D6":

1. Select columns BF and BG and copy them (Ctrl+C)


2. Right click on the selection and click on "Insert Copied Cells"; now the sheet ends in column BI


3. Select columns BH and BI


4. Right click on the selection and click on "Insert"; this shift BH and BI to the right, creating BJ and BK, while BH and BI become blank columns


5. Select columns BF and BG and copy them (Ctrl+C)


6. Select columns BH and BI and paste (Ctrl+V) the columns copied on the previous item


7. Select columns BH and BI and copy them (Ctrl+C)


8. Select columns BJ and BK and paste (Ctrl+V) the columns copied on the previous item; now BJ6 contains the formula "=AnotherSheet!E6" and BK6 contains the formula "=AnotherSheet!F6"


9. Select columns BF and BG, right click on the selection and click on "Remove"; now the sheet ends in columns BI

The result is as requested in the question: BF6 contains "=AnotherSheet!C6", BG6 contains "=AnotherSheet!D6", BH6 contains "=AnotherSheet!E6" and BI6 contains "=AnotherSheet!F6".

I'm still open for a better suggestion.

Select both the column before and after the hidden ones by holding down the left button then right click and select unhide, alternatively select resize and set the size to 50.

Alternatively, you can select all -A and either un-hide or format->set col width.

Click in the column BG column header. 
Then, while holding down the mouse button, drag the mouse to the right. 
A tooltip saying “16326C” will appear; release the button.

You have now selected 16,326 columns – BG through XFD. 
You should now be able to Unhide them.

(Tested on Excel 2007 on Windows 7.)