Counting Number of Columns

[Bluepolo3]

I am using Excel 2010. I have tried using the code:

Num_Col = Cells(1, 1).End(x1ToLeft).Column

to count the number of columns that have data and keep getting an error. Is this code incorrect? If so can anyone please help me out to count the number of columns being used.

Thanks

 

[rdw72777]

I use this, not sure if 2010 is different (I'm in 2007)

.Cells(1, Columns.Count).End(xlToLeft).Column

 

[rcbunting]

Bluepolo3, your formula is using a number 1 instead of a letter l in the constant 'xlToLeft' -- and in your case you'd want 'xlToRight' to make it work.

In order to do xlToLeft, it would look like rdw7277's answer above (no period at the beginning tho, i think?)

 

[Kenneth Hobson]

Welcome to the forum!

Ryan's method works fine if you wanted the column number in row 1 with data.

Sub Text_FindLastColumn()
  MsgBox FindLastColumn
End Sub

'Find the last used Column on a Worksheet:
Function FindLastColumn() As Integer
  Dim rLastColumn As Range
  If WorksheetFunction.CountA(Cells) > 0 Then
    'Search for any entry, by searching backwards by Columns.
    FindLastColumn = Cells.Find(what:="*", After:=[A1], _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious).Column
    Exit Function
    FindLastColumn = 1
  End If
End Function

Howsoever, if you really wanted the number of columns being used then:

acitvesheet.usedrange.Columns.Count

This could mean that column A or others up to the first used column are not counted.

 

[Bluepolo3]

Thank you everyone for the help! Much appreciated!