VBA Research uses VBA to get the number of rows that Excel arbitrarily columns

Iamlaosong

When the Excel file is processed with VBA, the column number of the keyword segment is often not known when programming. Need to know by the number of parameters, so. When we program, we cannot use this statement to take a valid number of rows:

Lineno = [B65536]. End (Xlup). Row ' find valid rows from bottom to top

The column name "B" in the above statement is assumed to be a variable. Can be implemented in the form of a string connection, namely:

pos_ems = "C"
Lineno = Range (pos_ems & "65536"). End (Xlup). Row

Assuming a column number, the following statement is used:

Pos_ems = 3
Lineno = Cells (65536, Pos_ems). End (Xlup). Row

Suppose that all the cells in the column need to be processed have values. You can also use the following statements:

Lineno = [B1]. End (xldown). Row ' from top to bottom to find the number of valid rows

orIneno = Cells (Pos_fst, Pos_ems). End (Xldown). Row
The pos_fst in the statement is the starting line of the valid data, the advantage of which is that the maximum number of rows in a worksheet is 65536 or 1048576, and the disadvantage is that the valid data has only one row or the column has null values. The number of valid rows will not be taken. This is also the main reason why this method is not used frequently.

The variable Pos_ems in the above statement can read the value of a cell so that it is ready to be set when confronted with different files:

    POS_FST = Cells (2, 7)    Pos_ems = Cells (3, 7)    Pos_sav = Cells (4, 7)        Lineno = [B65536]. End (Xlup). Row           ' number of files for        Unit_num = 5 to Lineno                 ' file loop            datfile = Cells (Unit_num, 2)                              ' filename        datfullname = Thisworkbook.path & "\" & Datfile        If Dir (datfullname, vbnormal) <> vbNullString            Then Workbooks.Open filename:=datfullname        ' Open order file            If application.version >= ' 12.0 ' and Activeworkbook.fileformat =                MaxRow = Cells (1048576, Pos_ems). End (Xlup). Row            Else                maxrow = Cells (65536, Pos_ems). End (Xlup). Row            End If        Else            MsgBox "Data file does not exist. ", vbOKOnly," Iamlaosong "            Exit Sub        End If

。。

。

Excel has a different version number. The maximum number of rows and columns is different. The maximum number of rows in version 2003 is 65536 rows, and the maximum number of columns is 256 columns (the last column, iv). The maximum number of Excel2007 and subsequent version numbers is 1048576 rows, and the maximum number of columns is 16384 columns (the last column XfD).
open a blank new Excel table, press CTRL + DOWN ARROW to see the maximum number of rows, and press CTRL + RIGHT ARROW to see the Maximum column label (if you want to display the number of columns). You can enter a =column () carriage return in a cell in the right column, where the number is the maximum number of columns.

VBA Research uses VBA to get the number of rows that Excel arbitrarily columns