I have the code below which currently selects a fixed range and hides columns where the value of the cell in row 1 is not blank but is zero.
Range("L1:YF1").Select Selection.EntireColumn.Hidden = False For Each Cell In Selection Count = Count + 1 Next Cell Range("L1").Select For Ch = 1 To Count If ActiveCell.Value <> "" And ActiveCell.Value = 0 Then Selection.EntireColumn.Hidden = True Else End If ActiveCell.Offset(0, 1).Range("$A$1").Select Next ChRange("$K$2").Select
Question is, line 1 selects a fixed Range but I would like to change this to the equivalent of "Shift + Ctrl + End" ie from current cell to the last cell in the row. End Right won't work because there are gaps.
I could go to XFD1 and then do an End Left to determine the last column but how do I then convert that into the range to be selected.
For the second section of the code where I am using ActiveCell.Offset to move across one column at a time. I assume this is the "right" way to do it because I assume that if I did it with "For Each Cell in Selection.... Next Cell" I would end up hiding the whole selection each time. Happy to be corrected on that point.
As always, much appreciated.
Thanks
Rob H
Microsoft ExcelVisual Basic Classic
Last Comment
Rob Henson
8/22/2022 - Mon
Patrick Matthews
Why bother selecting anything at all?
Dim rng As RangeDim cel As RangeSet rng = Range(Range("L1"), Cells(1, Columns.Count).End(xlToLeft))For Each cel In rng.Cells If cel <> "" and cel = 0 Then cel.EntireColumn.Hidden = True End IfNext
Note that by using Columns.Count, this is also compatible with pre-2007 versions of Excel, which were limited to 256 columns.
Rob Henson
ASKER
That looks great Patrick!!
Do I have to Unhide columns to start with? If a column was previously zero value it would get hidden but if there is now a value in that column I need it unhidden; hence line 2 unhiding the whole range.
There are no other hidden columns so I could select the whole sheet to Unhide if so required.
Open in new window
Note that by using Columns.Count, this is also compatible with pre-2007 versions of Excel, which were limited to 256 columns.