Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns by Macro
Thanks to Chip Pearson for supplying the following formula
But can somebody explain it to me !! There are a couple of functions that are new to me. Just as a refresher, I am trying to hide the 2nd last column of a spreadsheet, and that column is a different column number each month.....but always the 2nd last. Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft) (1, 0)) _ .EntireColumn.Hidden = True Regards Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns by Macro
Michael,
The formula works as follows: Range(Rng1, Rng2).EntireColumn.Hidden = True where Rng1 = Range("A1") Rng2 = Cells(1,Columns.Count).End(xlToLeft)(1,0) The first should be self-explanatory. Lets break down the second. It starts with Cells(1,Columns.Count) or Cell(1,256) which is the same as Range("IV1") Then, from this range, it uses End(xlToLeft) to skip empty columns moving to the to left, arriving at the last non-blank entry in row 1. Finally, it uses (1,0) to move one additional column to the left, which is the second-to-last non-blank entry in the row. Combining the two ranges, we get a range starting at A1 and ending at the second to last non-blank column in row 1. We finally use EntireColumn to get a reference to the entire columns in that range, and set the Hidden property to True. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Michael Mitchelson" wrote in message ... Thanks to Chip Pearson for supplying the following formula But can somebody explain it to me !! There are a couple of functions that are new to me. Just as a refresher, I am trying to hide the 2nd last column of a spreadsheet, and that column is a different column number each month.....but always the 2nd last. Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft) (1, 0)) _ .EntireColumn.Hidden = True Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
macro and deleting or hiding columns | Excel Discussion (Misc queries) | |||
Hiding columns without usimg a macro | Excel Worksheet Functions | |||
Hiding Columns | Excel Programming | |||
Hiding columns in VB | Excel Programming |