Finding the last occupied row or column
Make use of the End property of the range object which does the equivalent of pressing control and one of the arrow keys.
To find the last occupied row in column A you can use:
Dim lngRow As Long lngRow=Sheets("Sheet1").Range("a1").End(xlDown).Row
However if your data as gaps in it then this will find the last cell before the cell with a gap. Try it yourself using the keyboard Control+Down Arrow. Therefore if you want to find the true last occupied cell it is better to start at the bottom and work up:
Dim lngRow As Long lngRow=Sheets("Sheet1").Range("a65536").End(xlUp).Row
To find the last occupied column with continous data you can use:
Dim lngCol As Long lngCol=Sheets("Sheet1").Range("a1").End(xlToRight).Column
or as before with non-continous data starting from the end and working back:
Dim lngCol As Long lngCol=Sheets("Sheet1").Range("IV1").End(xlToLeft).Column
