In excel 2003 consist 65536 rows and 256 columns, that why I always declare variable which refer to rows count as long and column count as integer. In Excel 2007 the number of rows and column increase to 1048576 for row and 16384 for column, meaning row increase more than 16 times and column 64 times. This is to tell us that when dealing with large number of rows and columns we need more powerful computer than before to run your macro other wise it will be very slow. I do not know the reason but for sure to suit our needs to store more and more data and keep growing, some more our computer ability also increase day by day. To check number of rows and column open your Excel and simply press Ctrl + Arrow Down for rows and Ctrl + Arrow Right for Column but for column your need to change reference style from A,B,C.. to numbers. To do this changes simply go to Tools–>Option–> Select General Tab and tick R1C1 reference style and click OK.
Option Explicit Sub FindLast() Dim LstRow As Long Dim LstCol As Integer If WorksheetFunction.CountA(Cells) = 0 Then Selection.End(xlDown).Select Selection.End(xlToRight).Select LstRow = ActiveCell.Row LstCol = ActiveCell.Column MsgBox ("Total rows = " & LstRow & ", Total Columns = " & LstCol) Else MsgBox ("Please tested with blank sheets") End If 'To scroll back to original position With ActiveWindow .ScrollColumn = 1 .ScrollRow = 1 End With Range("A1").Select End Sub
The above will count for you but you must run at an empty sheets, after run message prompt number of rows and columns availabe.
Click here if you have any new project! Let us create for u for free.