Multiple methods for calculating the number of rows and columns in the WPS table et Excel using VBA

Source: Internet
Author: User

use VBA to calculate the number of rows and columns in the WPS table et Excel The number of rows in Excel above each method, and the following is the number of columns in Excel. Method 1: ActiveSheet.UsedRange.Rows.CountActiveSheet.UsedRange.Columns.Count disadvantage: Sometimes it may be larger than the actual number, because if you clear the last few rows (column) data (not the whole row or column), the command still returns Before the value is cleared. That means it's empty now, but you've used it. Method 2:activesheet.range ("A65535"). End (Xlup). Rowactivesheet.range ("IV1"). End (xlToLeft). The column can be abbreviated as: ActiveSheet. [A65536]. End (Xlup). Rowactivesheet. [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). RowActiveSheet.Cells.SpecialCells (xlCellTypeLastCell). Column disadvantage: When a worksheet is deleted or cleared, it becomes larger than it actually is. Method 4:activesheet.usedrange.specialcells (xlCellTypeLastCell). RowActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell). Column disadvantage: 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") 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). RowActiveSheet.Cells.Find (What:="*", AFTER:=[A1], searchorder:=xlbycolumns, SearchDirection:=xlprevious). The column effect is more commonly used in Method 2 than Method 1 and Method 2. 

Multiple methods for calculating the number of rows and columns in the WPS table et Excel using VBA

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.