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

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