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