On top of the page you can read this
The Example macro's use one of the functions below, the macro's will not work if you
don't copy them in the module also.
The functions needed for :
1: Finding the last row with data
2: Finding the last column with data
Where do I place the macro's and the functions?
1. Alt-F11
2. Insert>Module from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs
http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA
'*********************************************************
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
'*********************************************************
You can also check one row or column to find the last cell with a value.
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
This will give you the last row with data in Column A + 1 Row
Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Column
This will give you the last column with data in Row 1 + 1 column
Be aware that if you copy a range with also empty cells in it, It is possible that
the next time you copy to Sheets("Sheet2")some lines will be overwritten.
Use the Functions to avoid this kind of problems.
