Number of Rows and columns
VBA code can easly access each cell of an Excel worksheet once the number of rows and columns is known. The SpecialCells function can be used to activate the last cell in a worksheet. Once the last cell is activated, the number of rows and the number of columns can be saved as macro variable.
It is important to save key indicators of the current environment as macro variables so they can later be restored. The active workbook name, worksheet name, row number, and column number are some of the basic elements of the environment.
Once the number of rows and the number of columns are saved as macro variables, the double do loop can access each cell. In this example counting the number of blank cells is accomplished. The final step is to display a message block with the information.
SAS Fifth Dimension Macro-List Number Rows Columns
Review the following code, copy it to Excel and execute it.
Sub ListNumberRowsColumns() ' Saving the current environment BOOK_NAME = ActiveWorkbook.NAME SHEET_NAME = ActiveSheet.NAME CURROW = ActiveCell.ROW CURCOL = ActiveCell.Column ' Activate the last cell in the worksheet Selection.SpecialCells(xlLastCell).Activate NUMROWS = ActiveCell.ROW NUMCOLS = ActiveCell.Column ' Restore active cell cells(CURROW,CURCOL).Select ' Access every cell in a work book For II = 1 To NUMROWS For JJ = 1 To NUMCOLS If Cells(II, JJ) = "" _ Then BLANKS = BLANKS + 1 Next Next ' Display a message block MsgBox "Active Workbook name" & vbCrLf & _ BOOK_NAME & vbCrLf & vbCrLf & _ "Active Sheet name" & vbCrLf & _ SHEET_NAME & vbCrLf & vbCrLf & _ "Number of rows=" & NUMROWS & vbCrLf & _ "Number of columns=" & NUMCOLS & _ vbCrLf & vbCrLf & BlANKS & " Blank cells" End Sub