find number of columns used in a excel worksheet using macro

Question

I am working on excel sheets using macros.

I wanted to count the number of columns used in the sheet. I used:

ColLen = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

on a particular workbook and it gave a value one greater than the used number of columns. On other normal excel workbooks, it is giving the correct value.
Another command is giving the correct value for number of columns, which is:

ColLen = Activeworksheet.Cells(1, Columns.count).End(xlToLeft).Column

Its other part i.e. number of rows used giving correct value in all sheets.

RowLen = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).Row

Any clue what can be wrong in that sheet? I can't explain its details as told by my company. So, it may be difficult to answer as it may be illogical to ask. If you have any idea, please tell.

UsedRange is highly unreliable. Use `.Find` to find the columns. See [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) link for rows which you can amend for columns. — Siddharth Rout, Jan 30 '14 at 11:34
[HERE](http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column) is the link which I actually was looking for :) — Siddharth Rout, Jan 30 '14 at 11:35

score 2 · Accepted Answer · answered Jan 30 '14 at 11:59

To get columns, the most reliable code is: (it works under all cases)

Set rng = ws.Cells.Find(What:="*", _
            After:=ws.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False)

If rng Is Nothing Then
    LastCol = 1
Else
    LastCol = rng.Column
End If

`

find number of columns used in a excel worksheet using macro

1 Answers1