Get the last column number while the column is hidden

Question

I need to get the last column number on the second row, while the cited column is hidden.
The below code outputs wrong result if the last column is hidden.

Sub Last_column_number_even_is_hidden()
 
    Dim ws As Worksheet, lastCol_n As Long
 
    Set ws = ActiveSheet
     lastCol_n = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
      MsgBox ("Last column number is " & lastCol_n)
End Sub

Please, try `Debug.Print ws.UsedRange.Columns.count - ws.UsedRange.column + 1` — FaneDuru, Feb 09 '23 at 12:59
@FaneDuru ,As current setup, It gives correct result. But as you know `UsedRange` is not a reliable method and I need the Last column on **second row** and not the one on all sheet. — Waleed, Feb 09 '23 at 13:08
I've just supplied a method for the second row, not caring about the reliability of Used Range... You firstly need to make the code referring the correct (whole) range. Used Range does that, not caring about columns visibility. Then, using its second row and the classical way, you will obtain the correct answer... If you do not see it, refresh the page (this one)... Try clearing cells on the second row last column. — FaneDuru, Feb 09 '23 at 13:11
The suggested solution was wrong. I tested it in a bad environment and being in a hurry I copied a wrong code line, anyhow. I do not have time now to dig deeper... I deleted the comment in discussion. — FaneDuru, Feb 09 '23 at 13:36
If your data doesn't contain any complete empty columns, consider to use `CurrentRegion`. Else deep dive into https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba/11169920#11169920 — FunThomas, Feb 09 '23 at 13:42

FaneDuru · Accepted Answer · 2023-02-09T14:20:32.927

3

It looks that only Find does not care about the hidden cells in a range... Please, try the next way:

Sub testLastColEvenHidded()
    Dim ws As Worksheet, lastCol_n As Long, rng2 As Range
 
    Set ws = ActiveSheet
    Set rng2 = ws.UsedRange.rows(2)
 
    lastCol_n = rng2.Find(What:="*", After:=rng2.cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
                
    Debug.Print lastCol_n
End Sub

UsedRange may be not reliable, but only adding to the range... So, using it you can extract a full slice of a specific row to process it using Find, which does not care about not visible cells.

edited Feb 09 '23 at 14:20

answered Feb 09 '23 at 14:01

FaneDuru

38,298
4
19
27

Fantastic, I tried and it works correctly in all cases – Waleed Feb 09 '23 at 14:14
@FaneDuru It is a good approach, I would change a part of it to `Dim ra As Range Set ra = rng2.Find(What:="*", After:=rng2.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) If Not ra Is Nothing Then Debug.Print ra.Column Else Debug.Print -1 End If` But be aware that merged cells in the row can give bad results And the other minor thing is, that all these find things will stay with the users find dialog. But i think this is the less problem. – Red Hare Feb 09 '23 at 17:29
@Red Hare Using `Find` to search for "*" **will always return a range**. The question was specifying something about a (hidden) column and a row where to search for. So, no place for any **last** cell to be merged on column and even a merged cell by the row will return its left cell, which would also be correct. If you want counting the sheets in the pie, you can even check if the found cell is merged, extract its first cell and will obtain the same... – FaneDuru Feb 09 '23 at 18:16
@FaneDuru I'm absolutely sorry if I offended you. find for * won't find a range if the row is empty - sorry - but you are right, there is no sense in looking for a flaw in a given code by you. Sorry again that I looked into your answer. I will not do it again, promise! – Red Hare Feb 09 '23 at 18:46
1

@Red Hare No, I did not feel being offended! I only tried explaining that merged cells could not determine the above solution to not work as it was required. I also can admit that I was wrong and if you have some arguments to sustain a different scenario, I am open to discuss. And it is good to always react if something seems to not be good enough and be improved. Only in this way all of us can learn and progress... – FaneDuru Feb 09 '23 at 18:56

Get the last column number while the column is hidden

1 Answers1

Linked