Home Counting rows and columns for named ranges in vba

# Counting rows and columns for named ranges in vba

Ski bum
1#
Ski bum Published in 2017-12-07 23:06:30Z
 I am trying to count the number of rows and columns in vba from a named range in excel. The row and column counts are then used in an 2D array to perform other calculations. The named range is "CF_Inputs" which is from A2:Z60 in a worksheet called "Price_Volumes_Inputs". I can't seem get a row & column count using named ranges, so I used the code below with the offset method to count rows and columns. Is there a way to do this using the actual named range? I'm new to the whole programming thing, so I apologize for the simple question. Dim Inputs As Variant Sheets("Price_Volumes_Input").Activate Inputs = Range("A2", Range("Z2").End(xlDown).End(xlToRight)) 
Jamie Riis
2#
Jamie Riis Reply to 2017-12-08 00:45:53Z
 Welcome to the world of VBA programming. If you're going to be doing Excel VBA programming then you need to bookmark Chip Pearson's website. I've found it very useful. The link is http://www.cpearson.com/Excel/MainPage.aspx There are many ways to do this. For example one simple way is Dim dataRows As Long Dim dataCols As Long Dim workArray as Variant With ActiveWorkbook.Sheets("Price_Volumes_Input").Range("CF_Inputs") dataRows = .Rows.Count dataCols = .Columns.Count End With Dim workArray() As Variant Redim workArray(dataRows, dataCols) as Variant Dim i as Long, j as Long For i = 0 to dataRows - 1 For j = 0 to dataCols - 1 'do the calculations Next j Next i 
 You need to login account before you can post.
Processed in 0.300043 second(s) , Gzip On .