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

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" <(E-Mail Removed)> wrote in message
news:54235AAC-EBB2-4F41-8D1F-(E-Mail Removed)...
> is there a way to find last column that has data in a given worksheet??
> without selecting the range of data.

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.

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


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

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" <(E-Mail Removed)> wrote in message
news:02073734-0f6d-42b3-9853-(E-Mail Removed)...
> Can't you just use
> ActiveCell.SpecialCells(xlLastCell).Select
>
>
> On Jun 13, 2:43 pm, guest <gu...@discussions.microsoft.com> wrote:
>> is there a way to find last column that has data in a given worksheet??
>> without selecting the range of data.
>

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" <(E-Mail Removed)> wrote in message
> news:54235AAC-EBB2-4F41-8D1F-(E-Mail Removed)...
> > is there a way to find last column that has data in a given worksheet??
> > without selecting the range of data.
>
>

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" <(E-Mail Removed)> wrote in message
> news:02073734-0f6d-42b3-9853-(E-Mail Removed)...
> > Can't you just use
> > ActiveCell.SpecialCells(xlLastCell).Select
> >
> >
> > On Jun 13, 2:43 pm, guest <gu...@discussions.microsoft.com> wrote:
> >> is there a way to find last column that has data in a given worksheet??
> >> without selecting the range of data.
> >
>
>

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
newsE6926F5-8470-42F8-87AF-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:54235AAC-EBB2-4F41-8D1F-(E-Mail Removed)...
>> > is there a way to find last column that has data in a given worksheet??
>> > without selecting the range of data.
>>
>>

On Jun 13, 10:43*pm, guest <gu...@discussions.microsoft.com> 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

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