Count columns and rows from a defined range

[piercebrosnan]

Hello,
I would like to modify my code below to count columns and rows from a different range. Currently, the code below only reads from A1 of my data table. My data table starts at F7 with n columns and y rows. Each time I run the macro it does not recognise the range but only from A1.

Could you help to modify this code to read for a defined range?

Thanks in advance.

Please Login or Register  to view this content.

Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double


Worksheets(2).Activate
' Count number of columns and rows in use...
iColumnCount = ActiveSheet.UsedRange.Columns.Count
lRowCount = ActiveSheet.UsedRange.Rows.Count

' Cycle through each non-empty column...
For iCol = 1 To iColumnCount
' Use the Average and StDev Excel functions to
' calculate values for each column...
dAverage = WorksheetFunction.Average(Columns(iCol))

' Place results below existing data...
Cells(lRowCount + 2, iCol).Value = dAverage

Next iCol

End Sub

Please Login or Register  to view this content.

[patel45]

Please Login or Register  to view this content.

[piercebrosnan]

Thanks,
I still get the column and row count as 0.
Any suggestions?
Thanks,

PS: This was also posted on http://www.ozgrid.com/forum/showthre...goto=newpost**

[AB33]

Please use code tags with your code.
The code works for me. The only reason I can think it may not work may be you are using index numbering of your sheet. You should change to either the actual sheet name, or activesheet. Index no 2 may change.

Please Login or Register  to view this content.