Avatar of Rob Henson
Rob Henson
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Determine Range to Select

Hi All,

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.
    Selection.EntireColumn.Hidden = False
    For Each Cell In Selection
        Count = Count + 1
    Next Cell
    For Ch = 1 To Count
        If ActiveCell.Value <> "" And ActiveCell.Value = 0 Then
        Selection.EntireColumn.Hidden = True
        End If
        ActiveCell.Offset(0, 1).Range("$A$1").Select
    Next Ch

Open in new window

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.
Rob H
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Patrick Matthews

Why bother selecting anything at all?

Dim rng As Range
Dim cel As Range

Set 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 If

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.
Rob Henson

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.
Patrick Matthews

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Patrick Matthews

I see our comments crossed paths :)
Your help has saved me hundreds of hours of internet surfing.
Rob Henson

Excellent amendment, thank you!!