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, _ MatchCase:=False) If rng Is Nothing Then LastCol = 1 Else LastCol = rng.Column End If