Areas, rows and colums
dtrudell,
A couple of things. First usedrange.rows.count and usedrange.columns.count
can return total rows and columns count
Second
selection.areas.count
DOES NOT APPLY TO THE AREAS. That count how many non-continuous selections
you have.
the following code should select every non-blank, non-formula cell in your
worksheet
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
This code will select non-blank, formula cells
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
and this code will select EVERY NONBLANK cell in your workbook
Cells.Select
Set r1 = Selection.SpecialCells(xlCellTypeConstants, 23)
Set r2 = Selection.SpecialCells(xlCellTypeFormulas, 23)
Union(r1, r2).Select
once you have those select your selection.areas.count should display total
areas
--
When you lose your mind, you free your life.
"dtrudell" wrote:
I am trying to write a code to help analyze data and need to determine the
number of areas, rows and columns in each worksheet that is accessed.
Based on help searches, I have written the following crude code but it does
not work for all worksheets. Of great concern is that the
"selection.Areas.count" does not give me anything more than "one area". The
Rows and Columns count property gives various answers depending on the layout
of the worksheet. Does anyone have any suggestions?
Code:
Sub Rows_Cols()
' procedure counts the number of rows and columns
Dim nAreaCnt As Integer 'Determines if the sheet is formated properly for
analysis
Dim nRowCnt As Integer 'Stores the number of Rows in the sheet
Dim nCowCnt As Integer 'Stores the number of Columns in the sheet
nAreaCnt = Selection.Areas.Count
MsgBox "Number of Areas " & nAreaCnt
If nAreaCnt = 1 Then
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
nColCnt = Selection.Columns.Count
nRowCnt = Selection.Rows.Count
MsgBox "Number of Rows " & nRowCnt
MsgBox "Number of columns " & nColCnt
Else
MsgBox "Worksheet has empty rows or columns. Please fill sheet"
End If
End Sub
|