Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rows and colums | Excel Discussion (Misc queries) | |||
Inserting Rows in multiple areas | Excel Discussion (Misc queries) | |||
How do change rows to colums AND columns to rows | Excel Discussion (Misc queries) | |||
Print few rows with many colums so that rows wrap on printed pages | Excel Discussion (Misc queries) | |||
How do I name the colums and rows? | New Users to Excel |