P
pcorcele
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
Thanks
Ian M
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Ben McClave
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
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
G
GS
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
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
B
Ben McClave
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
Thanks for the notes. I guess I shouldn't try to write code after dinner - my brain must have been a bit foggy!
Ben
G
GS
Ben McClave brought next idea :
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
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
1
1404ottawa
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