find number of columns used in a excel worksheet using macro

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.


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, _

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


Need Your Help

Every 500 times do a Sleep()

php sleep

my database has 25000 rows.

Repeat UILocalNotification on Specific day

ios iphone objective-c datepicker localnotification

I need to set UILocalNotification, I just need to take hour and minute from DatePicker and need to set specific date ( like : Monday ) and repeat it on every Monday.