Counting rows and columns for named ranges in vba

Question

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))

Worksheets("Price_Volumes_Inputs").Range("CF_Inputs").Rows.Count — Scott Craner
– Scott Craner, Commented Dec 7, 2017 at 23:19
Thanks for the feedback very helpful. I was able to use the code for a modified row and column count, and also to populate a dynamic array. Sheets("Input").Activate Dim Row_Count As Long Dim Column_Count As Long Row_Count = Worksheets("Input").Range("CF_Inputs").Rows.Count Column_Count = Worksheets("Input").Range("CF_Inputs").Columns.Count Inputs = Worksheets("Input").Range("CF_Inputs", Range("CF_Inputs").End(xlDown).End(xlToRight)) — Ski bum
– Ski bum, Commented Dec 9, 2017 at 20:28

Community · Accepted Answer · 2018-05-08 21:49:49Z

2

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

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Dec 8, 2017 at 0:45

Jamie Riis

4113 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Counting rows and columns for named ranges in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related