macro to find bottom of column
 

Will someone help me with a macro that will go and any given col and go down until if finds the last entry in that col
Thanks
Ian M

macro to find bottom of column
 

Ian,

You can adapt this macro to suit your needs.

Ben

Sub ShowLastRow()
Dim Col As String
Dim XLRows As Long
Dim rRange As Range 'Optional

'Set number of rows based on XL version
If Application.Version < 12 Then
XLRows = 65536
Else
XLRows = 1048576
End If

'Get column letter or number
Col = InputBox("Enter a column letter or number")

On Error GoTo ErrorHandler

'If numeric, use Cells method, otherwise use Range method to find last row
If IsNumeric(Col) Then
MsgBox Cells(XLRows, Col * 1).End(xlUp).Address
'Set rRange = Cells(XLRows, Col * 1).End(xlUp)
Else
MsgBox Range(Col & XLRows).End(xlUp).Address
'Set rrange = Range(Col & XLRows).End(xlUp)
End If
Exit Sub

ErrorHandler:
If Err.Number < 0 Then
MsgBox "Could not find the last row for column " & Col
Err.Clear
End If

End Sub

macro to find bottom of column
 

There's way too much performance overhead for my liking what with all
the testing done in your code! Just thought you might appreciate the
following...

Columns(1) and Columns("A") return the same reference, so..

Dim vRef As Variant
vRef = InputBox("Enter a column letter or number")

...will return a number or string value relative to user input. So...

Columns(vRef) will evaluate to either Columns(1) OR Columns("A")!
===

Your code will fail if the user is working on an early version file in
XL2007 or later because it determines the number of rows based on
version, *NOT* the actual worksheet!

Rows.Count
..will return the number of rows on a worksheet regardless of
version. So...

lLastRow = Rows.Count.Row

...will get you the number of rows on a worksheet.
===

You use...

On Error GoTo ErrorHandler

...and so testing if an error occured there isn't required since
execution will only go there when Err.Number is not zero!
===

Sub ShowLastRow()
Dim vRef As Variant, rng As Range

vRef = Application.InputBox("Enter a column letter or number",
Type:=3) '//accept numbers or text only
If vRef = False Then Exit Sub '//user cancels

On Error GoTo ErrExit
Set rng = Cells(Rows.Count, Columns(vRef)).End(xlUp)
MsgBox rng.Address: rng.Activate

NormalExit:
Exit Sub

ErrExit:
MsgBox "Could not find the last row for column " & vRef
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

macro to find bottom of column
 

Garry,

Thanks for the notes. I guess I shouldn't try to write code after dinner - my brain must have been a bit foggy!

Ben

macro to find bottom of column
 

Ben McClave brought next idea :
Garry,

Thanks for the notes. I guess I shouldn't try to write code after dinner -
my brain must have been a bit foggy!

Ben

Ben,
I can appreciate your comment. I used to provide rather long,
self-documenting code that showed the steps fairly clearly. I now try
to provide code that's more efficient, but as self-documenting as
possible so readers can understand what it's doing.<g

Personally, I'd turn this sub into a reusable function that returns
either the row number when passed the column reference...

Function GetLastRow(vPos As Variant) As Long
GetLastRow = Cells(Rows.Count, vPos).End(xlUp).Row
End Function

...and use it like this...

Sub ShowLastCell()
Dim vRef As Variant, rng As Range
vRef = Application.InputBox("Enter a column letter or number", _
Type:=3) '//accept numbers or text only
If vRef = False Then Exit Sub '//user cancels

Set rng = Cells(GetLastRow(vRef), vRef)
MsgBox rng.Address: rng.Activate
End Sub
===

Conversely...

Function GetLastCol(lPos As Long) As Long
GetLastCol = Cells(lPos, Columns.Count).End(xlToLeft).Column
End Function
===

And finally...

Function GetLastCell(Optional Row&, Optional Col&, _
Optional IsRow As Boolean = True) As String
If Row = 0 Then Row = ActiveCell.Row
If Col = 0 Then Col = ActiveCell.Column
If IsRow Then
GetLastCell = Cells(Rows.Count, Col).End(xlUp).Address
Else
GetLastCell = Cells(Row, Columns.Count).End(xlToLeft).Address
End If
End Function

...where you can specify last cell in a row OR column and get its
address. Use it as follows:

Last row in ActiveCell column:
Range(GetLastCell()).Select

Last row in column3:
Range(GetLastCell(, 3)).Select
*OR*
Range(GetLastCell(Col:=3)).Select

Last column in ActiveCell row:
Range(GetLastCell(IsRow:=False)).Select

Last column in row3:
Range(GetLastCell(3, , False)).Select
*OR*
Range(GetLastCell(Row:=3, IsRow:=False)).Select

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

macro to find bottom of column
 

On Tuesday, October 30, 2012 6:51:20 PM UTC-4, pcorcele wrote:
Will someone help me with a macro that will go and any given col and go down until if finds the last entry in that col

Thanks

Ian M

The bottom suggestion worked the very best. Many Thanks
Ian M