G
guest
is there a way to find last column that has data in a given worksheet??
without selecting the range of data.
without selecting the range of data.
R
Rick Rothstein \(MVP - VB\)
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
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
J
Jim Thomlinson
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
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
R
Reitanos
Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.SpecialCells(xlLastCell).Select
R
Rick Rothstein \(MVP - VB\)
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
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
J
Jim Thomlinson
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.
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.
G
guest
Thanks Jim and Rick for your help.
Rick Rothstein (MVP - VB) said:
R
Rick Rothstein \(MVP - VB\)
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
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
N
NOPIK
ActiveWorksheet.UsedRange.Rows.Count
N
NOPIK
Of course!
UsedRange.Columns.Count
R
Reitanos
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
I could have sworn there was a command to do it, but my addled brain
can't seem to remember
R
Rick Rothstein \(MVP - VB\)
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
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
N
NOPIK
do not put anything into at least the first column)... then execute your
Thanks! I will fix my macros for this!
R
Reitanos
Ah, I found it:
Activesheet.Usedrange
ActiveCell.SpecialCells(xlLastCell).Select
But I guess it would be easier to just use:
Activesheet.Usedrange.Column
Activesheet.Usedrange
ActiveCell.SpecialCells(xlLastCell).Select
But I guess it would be easier to just use:
Activesheet.Usedrange.Column