VBA Research uses VBA to get the number of valid rows for any column in Excel

Iamlaosong

When using VBA to process Excel files, the key fields of the column number programming is often not known, need to pass parameter settings to know, therefore, when we program, we can not use such a statement to take the number of valid rows:

Lineno = [B65536]. End (Xlup). Row

The column name "B" in the above statement, if it is a variable, can be implemented in the form of a string connection, namely:

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

If the column number is given, the following statement is used:

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

The variable Pos_ems in the above statement can read the value of the 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)                              ' file name        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

。。。

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

VBA Research uses VBA to get the number of valid rows for any column in Excel