column count
 

is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

You can use this function...

Function MaxColInUse(Optional WS As Worksheet) As Long
Dim X As Long
Dim LastCol As Long
Dim Rw As Variant
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For Each Rw In .UsedRange.Rows
On Error Resume Next
LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column
For X = LastCol To 0 Step -1
If .Cells(Rw.Row, X).Value < "" Then
LastCol = X
Exit For
End If
Next
If LastCol MaxColInUse Then MaxColInUse = LastCol
Next
End With
End Function

Rick
"guest" wrote in message
...
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

Here is a function that returns the last column...

sub test
msgbox lastcolumn 'activesheet
msgbox lastcolumn(sheets("Sheet1"))
end sub

Public Function LastColumn(Optional ByVal wks As Worksheet) As Long
Dim lngLastColumn As Long

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
LastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If LastColumn = 0 Then lngLastColumn = 1
End Function
--
HTH...

Jim Thomlinson


"guest" wrote:

is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select


On Jun 13, 2:43 pm, guest wrote:
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

Not reliably. Try this... put some data in some of the rows out to Column 5
on a sheet and then put something in J3. Go to the Immediate window and
enter this...

? ActiveCell.SpecialCells(xlLastCell).Column

It should print out 10. Now issue this command in the Immediate window

Range("J3").Delete

That will remove the entry from J3 that you just put in it. Look at the
worksheet... the last used column is now 5. Go back and execute this line
again...

? ActiveCell.SpecialCells(xlLastCell).Column

It should still print out 10.

Rick



"Reitanos" wrote in message
...
Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select


On Jun 13, 2:43 pm, guest wrote:
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

Guest...

On an empty sheet this function returns zero. While that is correct you can
not reference column 0 which can lead to a run time error. Just something to
be aware of... The function I posted returns 1 on an empty sheet. That may or
may not be correct depending on what you want to do.
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

You can use this function...

Function MaxColInUse(Optional WS As Worksheet) As Long
Dim X As Long
Dim LastCol As Long
Dim Rw As Variant
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For Each Rw In .UsedRange.Rows
On Error Resume Next
LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column
For X = LastCol To 0 Step -1
If .Cells(Rw.Row, X).Value < "" Then
LastCol = X
Exit For
End If
Next
If LastCol MaxColInUse Then MaxColInUse = LastCol
Next
End With
End Function

Rick
"guest" wrote in message
...
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

Thanks Jim and Rick for your help.

"Rick Rothstein (MVP - VB)" wrote:

Not reliably. Try this... put some data in some of the rows out to Column 5
on a sheet and then put something in J3. Go to the Immediate window and
enter this...

? ActiveCell.SpecialCells(xlLastCell).Column

It should print out 10. Now issue this command in the Immediate window

Range("J3").Delete

That will remove the entry from J3 that you just put in it. Look at the
worksheet... the last used column is now 5. Go back and execute this line
again...

? ActiveCell.SpecialCells(xlLastCell).Column

It should still print out 10.

Rick



"Reitanos" wrote in message
...
Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select


On Jun 13, 2:43 pm, guest wrote:
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

I guess that situation needed to be handled (that is, if you could not be
sure it would be executed against an empty worksheet), the calling routine
could do something like this...

LastCol = MaxColInUse
If LastCol 0 Then
'
' Do whatever...
'
End If

which assumes LastCol would be put into use within the If-Then block itself.
If that was not the case for some reason, then this could be shortened to
this...

If MaxColInUse 0 Then
'
' Do whatever...
'
End If

although I can't think of any situation off the top of my head where the
latter would be the case.

Rick


"Jim Thomlinson" wrote in message
...
Guest...

On an empty sheet this function returns zero. While that is correct you
can
not reference column 0 which can lead to a run time error. Just something
to
be aware of... The function I posted returns 1 on an empty sheet. That may
or
may not be correct depending on what you want to do.
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

You can use this function...

Function MaxColInUse(Optional WS As Worksheet) As Long
Dim X As Long
Dim LastCol As Long
Dim Rw As Variant
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For Each Rw In .UsedRange.Rows
On Error Resume Next
LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column
For X = LastCol To 0 Step -1
If .Cells(Rw.Row, X).Value < "" Then
LastCol = X
Exit For
End If
Next
If LastCol MaxColInUse Then MaxColInUse = LastCol
Next
End With
End Function

Rick
"guest" wrote in message
...
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

column count
 

On Jun 13, 10:43*pm, guest wrote:
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.

ActiveWorksheet.UsedRange.Rows.Count

column count
 

Of course!
ActiveWorksheet.UsedRange.Rows.Count
UsedRange.Columns.Count

column count
 

I know that saving the workbook will reset that immediately.
I could have sworn there was a command to do it, but my addled brain
can't seem to remember :(


On Jun 13, 4:24 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Not reliably. Try this... put some data in some of the rows out toColumn5
on a sheet and then put something in J3. Go to the Immediate window and
enter this...

? ActiveCell.SpecialCells(xlLastCell).Column

It should print out 10. Now issue this command in the Immediate window

Range("J3").Delete

That will remove the entry from J3 that you just put in it. Look at the
worksheet... thelastusedcolumnis now 5. Go back and execute this line
again...

? ActiveCell.SpecialCells(xlLastCell).Column

It should still print out 10.

Rick

"Reitanos" wrote in message

...

Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select

On Jun 13, 2:43 pm, guest wrote:
is there a way to findlastcolumnthat has data in a given worksheet??
without selecting the range of data.

column count
 

Try this... insert a new sheet into your workbook, go to it, and put any
values you like in C3, D4 and E5 only (in other words, on a brand new sheet,
do not put anything into at least the first column)... then execute your
statement and see what you get.

Rick


"NOPIK" wrote in message
...
Of course!
ActiveWorksheet.UsedRange.Rows.Count
UsedRange.Columns.Count

column count
 

do not put anything into at least the first column)... then execute your
statement and see what you get.

Thanks! I will fix my macros for this!

column count
 

Ah, I found it:

Activesheet.Usedrange
ActiveCell.SpecialCells(xlLastCell).Select

But I guess it would be easier to just use:
Activesheet.Usedrange.Column


On Jun 13, 2:43 pm, guest wrote:
is there a way to findlastcolumnthat has data in a given worksheet??
without selecting the range of data.