 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
Copy the code in a Standard module of your workbook, if you just
started with VBA see this page.
Where do I paste
the code that I find on the internet
The macro's give you the row or column number of the last cell with data
in one row or one column.
Note: This code below will not
work correct if the last row with data is hidden.
Sub LastRowInOneColumn() 'Find the last used row in a Column: column A in this example Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With MsgBox LastRow End Sub Sub LastColumnInOneRow() 'Find the last used column in a Row: row 1 in this example Dim LastCol As Integer With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With MsgBox LastCol End Sub
Possible problems with xlCellTypeLastCell and UsedRange are:
The
last cell will only re-set when you save (or save/close/reopen the file). If
cell formatting is changed it will not reset the last cell, clearing the
data is not enough, you must delete the rows or columns then,
See:
http://www.contextures.com/xlfaqApp.html#Unused
So when using VBA you cannot rely on this macros if you want the last row
or column with data on your worksheet.
Last Row :
Sub xlCellTypeLastCell_Example_Row()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
MsgBox LastRow
End Sub
Sub UsedRange_Example_Row()
Dim LastRow As Long
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
MsgBox LastRow
End Sub
Last Column :
Sub xlCellTypeLastCell_Example_Column()
Dim LastColumn As Long
With ActiveSheet
LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
End With
MsgBox LastColumn
End Sub
Sub UsedRange_Example_Column()
Dim LastColumn As Long
With ActiveSheet.UsedRange
LastColumn = .Columns(.Columns.Count).Column
End With
MsgBox LastColumn
End Sub
In the example macros we call the function Last, this
function have two arguments
Argument 1 can be 1, 2 or 3
1
= last row
2 = last column
3 = last
cell
Argument 2 is the range where you want to search in
Copy the
example macros together with the function "Last" in a normal module of your
workbook. Note: There is no test if you exceed the amount
of rows and columns in the examples below, add that yourself.
Sub LastRow_Example()
Dim LastRow As Long
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last row
LastRow = Last(1, rng)
' After the last row with data change the value of the cell in Column A
rng.Parent.Cells(LastRow + 1, 1).Value = "Hi there"
End Sub
Sub LastColumn_Example()
Dim LastCol As Long
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Or use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last column
LastCol = Last(2, rng)
' After the last column with data change the value of the cell in row 1
rng.Parent.Cells(1, LastCol + 1).Value = "Hi there"
End Sub
Sub LastCell_Example()
Dim LastCell As String
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Or use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last cell
LastCell = Last(3, rng)
' Select from A1 till the last cell in Rng
With rng.Parent
.Select
.Range("A1", LastCell).Select
End With
End Sub
This is the "Last" function that we use in the macro's above :
Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long
Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function
If you only want to get the last Row or Column number of a worksheet you can also use the two seperate functions below if you want, but in the function above you have all options in one and also can check a range instead of the whole worksheet. you use the functions below like this :
Lr = LastRow(ActiveSheet)
Lc = LastCol(ActiveSheet)
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
1 : Tom Ogilvy posted this in the newsgroup. Find does
not find a cell containing the Null string "" entered when you do Edit=>Copy
and then Edit=>Paste Special, Values for a cell containing a formula like
=IF(A1="","",A1*1.21), which may or may not be desirable (end(xlup) does).
2 : Find not see cell formatting but only data, if this
is important for you see the xlCellTypeLastCell and UsedRange section of
this page to find the last row or column.
3 : If you
use merged cells (Please do not use merged cells) maybe you get unexpected
results.
It will give you the column number of the first cell and not the
last cell in the merged cells.