Creating Variable Range and inserting it to complete calculations

[DonEB]

Parts of my program (i.e., ranges) change depending on amount of data that is keyed in. I know the ranges minimum size (E6:I10) and the ranges maximum size of (E6:X25).

Based on previous research, I know how to identify last column and I know how to identify the last row. What I'm having difficulty with is determining how to utilize this information to create the dynamic range that can be inserted into another formula.

What I would like to be able to do is replace the "E6:K12" (fixed range below) with code that will allow it to be flexible and dynamically change as the data changes from month to month or quarter to quarter.

Any attempt I've made has resulting in one type of error or another.

With Sheets("CommonData2")
ColP = .Cells(6, Columns.Count).End(xlToLeft).Column

'Note: using column "C" does provide the best results for determining "last row". Other columns may contain a "0" thus giving an unintended/undesirable result.
LastRow = .Range("C6:C35").Find(What:="*", After:=.Range("C6:C35").Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Example of fixed range
rRange = ("E6:K12")
'rRange = ????????? '<=== Trying to create the dynamic range to insert into the variable and use in the code below

iMaxCount = WorksheetFunction.CountIf(Range(rRange), "=" & Max1) 'Helps determines if all players have played each other "max" times
End With

Any guidance would be appreciated.
Thanks

 

[James006]

Hi,
To gain flexibility, see two identical definitions :

Set rng = Range("E6:K12")
Set rng = Range(Cells(6, 5), Cells(12, 11))

In the second instruction, you can, obviously, have 4 separate variables : rw1, cl1 and rw2, cl2

Hope this will help

 

[DonEB]

Yes... this helped... I appreciate the time you took to review my problem and respond. Thank You.

 

[James006]

You are welcome