VBA gets the number of rows and columns in an Excel table

VBA Get EXCEL Number of rows and columns in a table

Beginners Excel Macro Children's shoes, always want to know the table contains data in the number of rows and columns, especially when the number of rows and the number of columns is uncertain. This avoids a lot of errors and can improve efficiency. But every time you use the Internet to find, always give a lot of useless answers, often can not find the desired results. The author is also every time use, temporary find always very headache. Accidentally found a blog, the above detailed records of different methods, the author tested several discoveries really useful. Share the content of your blog with the spirit of sharing Viva. We hope to help you.

Source: http://www.okexcel.com.cn/bbs/viewthread.php?tid=26

Note: The top of each method is the number of rows in Excel, and the following is the number of columns in Excel.

Method 1:

ActiveSheet.UsedRange.Rows.Count

ActiveSheet.UsedRange.Columns.Count

Disadvantage: It can sometimes be larger than the actual number, because if you clear the last few rows (columns) of data (not the whole row or column), the command still returns the value before the purge. That means it's empty now, but you've used it.

Method 2:

ActiveSheet.Range ("A65535"). End (Xlup). Row

ActiveSheet.Range ("IV1"). End (xlToLeft). Column

can be abbreviated as:

ActiveSheet. [A65536]. End (Xlup). Row

ActiveSheet. [IV1]. End (xlToLeft). Column

Disadvantage: You can only calculate the number of rows (columns) in which the last cell of a column (row) is located. This example returns only the number of rows in the last cell of column A.

Method 3:

ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell). Row

ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell). Column

Cons: When a worksheet is deleted or cleared, it becomes larger than it actually is.

Method 4:

ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell). Row

ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell). Column

Cons: When a worksheet is deleted or cleared, it becomes larger than it actually is.

Method 5:

Application.counta (ActiveSheet.Range ("a:a"))

Application.counta (ActiveSheet.Range ("1:1"))

You can only count the actual usage of one column (row), not the position of the last row (column). When the value of Method 2 is larger than this method, it indicates that there are blank blanks between the data in column A.

Method 6:

ActiveSheet.Cells.Find (what:= "*", after:=[a1], searchorder:=xlbyrows, searchdirection:=xlprevious). Row

ActiveSheet.Cells.Find (what:= "*", after:=[a1], Searchorder:=xlbycolumns, searchdirection:=xlprevious). Column

Effect Same Method 2

Method 1 and Method 2 are more commonly used in the above methods.

VBA gets the number of rows and columns in an Excel table