VBA how to determine the number of rows in a variable defined by a range stored in an array

57 Views Asked by At

The goal is to define a variable, in this case C, as the number of rows in a range stored in an array. The inputs (1-4) are defined using the "Defined Name" function in EXCEL. The (.Rows.Count) appears to only work with the range function. Any suggestions would be greatly appreciated. I can explain what I'm trying to do in more detail if need be.

Private Sub Pop_Click()
      Dim INPUT1 As Variant: Set INPUT1 = Range("INPUT1")
    Dim INPUT2 As Variant: Set INPUT2 = Range("INPUT2")
    Dim INPUT3 As Variant: Set INPUT3 = Range("INPUT3")
    Dim INPUT4 As Variant: Set INPUT4 = Range("INPUT4")
    Dim INPUTS As Variant: INPUTS = Array(0, INPUT1, INPUT2, INPUT3, INPUT4)
    
    Dim C As Variant: C = INPUTS(1).Rows.Count

    Dim SR1 As Integer: SR1 = 18
    Dim SC1 As Integer: SC1 = 4
    Dim ER1 As Integer: ER1 = 19
    Dim EC1 As Integer: EC1 = 15

    Range(Cells(SR1, SC1), Cells(ER1, EC1)) = INPUTS(1)
End Sub   
0

There are 0 best solutions below