f2e4
06-02-2008, 07:21 AM
I currently use the following to find the last active row:
With ActiveSheet
lastactiverow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
I thought I could just change the words 'Row' to 'Column' and 'Up' to 'Left' and it would find the last active column but no luck.
The only other code I have for columns is the following:
Set ws = ActiveSheet
With ws
Set ColFormula = .Cells.Find(What:="*", After:=Range("B2"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set ColValue = .Cells.Find(What:="*", After:=Range("B2"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
On Error GoTo 0
If ColFormula Is Nothing Then
lastcol = 0
Else
lastcol = ColFormula.Column
End If
If Not ColValue Is Nothing Then
lastcol = Application.WorksheetFunction.Max(lastcol, ColValue.Column)
End If
End With
But this is very long winded.
Does anyone know of a short version similar to my the row code aboev?
Thanks again.
With ActiveSheet
lastactiverow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
I thought I could just change the words 'Row' to 'Column' and 'Up' to 'Left' and it would find the last active column but no luck.
The only other code I have for columns is the following:
Set ws = ActiveSheet
With ws
Set ColFormula = .Cells.Find(What:="*", After:=Range("B2"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Set ColValue = .Cells.Find(What:="*", After:=Range("B2"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
On Error GoTo 0
If ColFormula Is Nothing Then
lastcol = 0
Else
lastcol = ColFormula.Column
End If
If Not ColValue Is Nothing Then
lastcol = Application.WorksheetFunction.Max(lastcol, ColValue.Column)
End If
End With
But this is very long winded.
Does anyone know of a short version similar to my the row code aboev?
Thanks again.