VBA - Determine first and last row in range in order to calculate correlation

[Rogue_Trader]

Hi guys, I'm currently using Excel 2010 on windows 7.

My issue is I have a sheet filled with data that has random separations (empty rows) for which I need to calculate correlations of the data in between the empty rows. For example I have data filling the range of D6:AA657 but with empty rows inserted randomly into the range.

My fixed column for calculating correlations is D and I want to go from column E all the way till it finds an empty header in Row3 (currently I just use 20 as a proxy in my code). Once I find the begining and end of the current segment of data I calculate correlations of Range D6:D* with Column E, F, G etc till it finds the empty header. It then enters the values along the empty row.

My current draft is below, it currently gives me errors, I was hoping someone could help me out a bit.

Sub Correl()
Dim rRngA As Range, RowLast As Long


Sheets("Buckets").Activate
Set RowLast = Range("D" & Rows.Count).End(xlUp).Row


Set rRngA = Range("D6", Range("D6").End(xlDown)) 'la colonne fixe
For i = 0 To 20
Set rRngB = rRngA.Offset(, 3 + i) ' allez à D
ans = Application.WorksheetFunction.Correl(rRngA, rRngB)
Cells(RowLast, 7 + i) = ans
Next


End Sub

Any help would be greatly appreciated,

Cheers,

RT

 

[Darren Bartrup]

Not sure if this will help or not, but sounds like a problem I had back in 2008. The code identifies all separate ranges within the workbook.

http://www.mrexcel.com/forum/excel-questions/309052-find-all-lists-workbook.html