Thread: Range definition: counting from the 2nd row down to whenever it ends

Hello,

I have defined a range, which I will use later in worksheetfunction.averageifs, but I want Excel to count from the 2nd row down to whenever it ends...

How do I put the lrow index in the range in the formula below?

AClinicElapsedDay is the range that contains numbers to be averaged later. (I just don't want Excel to average the title heading of that column)

code is:

Public Sub CCMPeriodInternalAnalysisVCH()
'calculating Average

Dim a3 As Long
Dim AClinicElapsedDay As Range

Dim lrow As Integer
lrow = Worksheets("A).Range("R2").End(xlUp).Row

Set AClinicElapsedDay = Worksheets("A").Range("R2") ' how do I put that lrow in the range here?

sub end

Thank you

Set AClinicElapsedDay = Worksheets("A").Range("R2:R" & lrow)

Regards, TMS

Hi Lifeseeker,

see below to get idea to include R2 till last range.

Sub CCMPeriodInternalAnalysisVCH()
 Dim rng As Range, i as double
Set rng = Range(Range("r2"), Range("r2").End(xlDown))
i = WorksheetFunction.Average(rng)
End Sub

Regards,
DILIPandey

<click on below 'star' if this helps>

Your ranges need to be the same size.

Also note that it is not good practice to define variables that can be mistaken for a range, for example, a3.

Option Explicit

Public Sub test()

Dim a3 As Long
Dim AClinicElapsedDay As Range
Dim AClinicPeriod As Range
Dim mPeriod As Range
Dim AClinicAnalyzable As Range
Dim lrow As Integer

lrow = Worksheets("A").Range("R2").End(xlUp).Row
Set AClinicElapsedDay = _
    Worksheets("A").Range("R2:R" & lrow)

Set AClinicPeriod = _
    Worksheets("A").Range("N2:N" & lrow)
Set mPeriod = _
    Worksheets("TEST").Range("B2")
Set AClinicAnalyzable = _
    Worksheets("A").Range("W2:W" & lrow)

a3 = Application.WorksheetFunction.AverageIfs( _
    AClinicElapsedDay, _
    AClinicPeriod, mPeriod, _
    AClinicAnalyzable, "Yes")
Worksheets("TEST").Range("A3") = a3

End Sub

Regards, TMS

It's the way you return the lrow variable.

lrow = Worksheets("A").Range("A2").End(xlUp).Row

gives a value of 1.

Try:

Option Explicit
Private Sub CommandButton1_Click()
'calculating Average in Days for VGH clinic, CCM by period
Dim b5 As Double
Dim AClinicElapsedDay As Range
Dim AClinicPeriod As Range
Dim mPeriod As Range
Dim AClinicAnalyzable As Range
Dim lrow As Integer

Set mPeriod = _
    Worksheets("Test").Range("B2")
lrow = Worksheets("A").Range("A" & Rows.Count).End(xlUp).Row
Set AClinicElapsedDay = _
    Worksheets("A").Range("B2:B" & lrow)
Set AClinicPeriod = _
    Worksheets("A").Range("A2:A" & lrow)
Set AClinicAnalyzable = _
    Worksheets("A").Range("C2:C" & lrow)

b5 = Application.WorksheetFunction.AverageIfs( _
    AClinicElapsedDay, _
    AClinicPeriod, mPeriod, _
    AClinicAnalyzable, "Yes")

Worksheets("Test").Range("B5") = b5

End Sub

Regards, TMS

The other folk?