Areas, rows and colums
 

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

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

Areas, rows and colums
 

Sub MultiAreas()
Range("A1,B2,C3,D4,E5,E1,D2,B4,A5").Select
MsgBox Selection.Areas.Count
End Sub

If you only select a single area, the count will always be 1. In your code
you don't show anything that selects prior to your areas.count test.

--
Regards,
Tom Ogilvy



"dtrudell" wrote in message
...
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

Areas, rows and colums
 

The statement
ActiveCell.SpecialCells(xlLastCell).Select
will select the last affected cell in a sheet.

You can then use
ActiveCell.Row
ActiveCell.Column

to retrieve the row and column number of that cell to see if if meets your
criteria.

HTH...Glenn Ray



"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