Home Counting rows and columns for named ranges in vba
Reply: 1

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300043 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO