Finding the start of second list after xlDown

[Ssignore08]

Hi all,

First time commenter, but I've learned a lot from reading other posts. I have a question that could cut my processing time in half, but I'm not sure if it's possible.

I have data in columns P and R starting in row 3 and going a random length. I want to perform SUMPRODUCT on the two ranges:
Set range1 = Range("P3", Range("P3").End(xlDown)).Select


Set range2 = Range("R3", Range("R3").End(xlDown)).Select

The tricky part is that I have a second set of data that starts 3 rows below the end of the previous data set and is also a random length. I also want to be able to perform a SUMPRODUCT on those ranges. Is there a way to use offset language with xldown? Can you use xldown twice in the same call?

something like:

Set range3 = Range(Range("P3").End(xlDown).Offset(3,0), Range("P3").End(xlDown).Offset(3,0).End(xlDown)).Select

If xldown can't be used twice in the same call then could the "selection" be useful?

I know I didn't post the SUMPRODUCT code, but that's not my concern here, I'm just wondering if finding those next two ranges is possible b/c if not, then my code is already optimal.

Since I currently just have 1 dataset per excel printout I am using the following code to do sumproduct manually for rows 5 to 43 (dataset length is random, but never exceeds 38 rows) and putting the result in cell(45,16). I then assign that value to a variable to use later in the code. I know I couldve used xldown in my ranges here, but I wrote this before I knew much, so I used record macro and was afraid of messing with it.

Range("P5").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-6]"
Range("P5").Select
Selection.AutoFill Destination:=Range("P5:P43"), Type:=xlFillDefault
Range("P5:P43").Select
Range("P45").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-2]C)"

'Copy cell P45 into the correct spot
Sleep 200
q = Cells(45, 16)

 

[Ssignore08]

I tried posting without looking at my spreadsheet, column P is where I was performing the manual sumproduct, the data is actually in columns J and K, not P and R, but that doesnt affect the question of whether I can find the second set of ranges correctly

 

[V_Malkoti]

Yes you can chain End().

Sub RangeTest()
    Set rng1 = Range(Range("P3"), Range("P3").End(xlDown))
    Debug.Print rng1.Address
    
    Set rng2 = Range(Range("P3").End(xlDown).Offset(4, 0), Range("P3").End(xlDown).Offset(4, 0).End(xlDown))
    Debug.Print rng2.Address
End Sub

By the way you have .Select in your assignment statement - invalid code.

 

[Ssignore08]

Thank you, that is perfect.

 

[Rick Rothstein]

Assuming your values are constants (not formulas), you could do it this way as well...

Sub RangeTest()

  Dim DataRanges As Range, Rng1 As Range, Rng2 As Range

  Set DataRanges = Columns("P").SpecialCells(xlConstants)

  Set Rng1 = DataRanges.Areas(1)
  Debug.Print Rng1.Address

  Set Rng2 = DataRanges.Areas(2)
  Debug.Print Rng2.Address

End Sub