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

 

[Rogue_Trader]

Hey guys,

No suggestions? Was my description not clear?


RT

 

[Joe4]

Starting from any cell, you can find the last row/column before the first blank (empty cell) it finds.

For example, starting from cell D3, to find the last column in row 3 before the first blank column to the right, you could use this:
lastCol=Range("D3").End(xlToRight).Column

To find the last row in column D with data (starting in row 3), before the first blank cell, you could use this:
lastRow=Range("D3").End(xlDown).Row

 

[Rogue_Trader]

Hi Joe4,

Thank you for the input, I am also having getting an object error when I try to set:

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

Have I not properly defined it?

 

[Joe4]

You only use the "Set" command when defining Ranges.
RowLast is NOT a range, it is a Long Integer. So remove the word "Set".

 

[Rogue_Trader]

Haha wow, I hadn't realized I will implement the changes after lunch. I will let you know how it goes. I appreciate the help Joe4!

 

[Rogue_Trader]

Ok so the advice worked like charm. I had one more issue. If I wanted the code to then find the next cell with data in column D and re-run the process what would be the best way to achieve this? i.e. I want to have a loop for D6 in my code that would run the code from D6 then after the the first empty cell find the next populated cell and re-run the process. I added my code below. It may seem simple i am just not that familiar with loop, especially not loops within loops.

Sub XY()
Dim rRngA As Range, rRngB As Range, ans As Double, lastCol As Long, lastRow As Long


lastCol = Range("D3").End(xlToRight).Column 'find the last column with a header
lastRow = Range("D6").End(xlDown).Row 'find last row, this is were the correlation value will be copied


Set rRngA = Range("D6", Range("D6").End(xlDown)) 'fixed column for calculating correlations


For i = 0 To lastCol
Set rRngB = rRngA.Offset(, 3 + i) 'select next column for correlation calculation
ans = Application.WorksheetFunction.Correl(rRngA, rRngB)
'MsgBox ans
Cells(lastRow + 1, 7 + i) = ans
Next i


End Sub

 

[Joe4]

I am on my way out the door, so I just through something together quickly that may help (at least you can see the logic).
If you run through this code snippet, you will see how it identifies the starting and ending columns of each data block:

    Dim lastColOnSheet As Long
    Dim lastColOnTable As Long
    
'   Find the last column on the whole sheet on row 3
    lastColOnSheet = Cells(3, Columns.Count).End(xlToLeft).Column
    
    lastColOnTable = 4

'   Loop through until you get past the end of the last data cell in that row    
    Do Until lastColOnTable > lastColOnSheet
        lastColOnTable = Cells(3, lastColOnTable).End(xlToRight).Column
        MsgBox lastColOnTable
    Loop

Hopefully, you will be able to incorporate that logic into your program.

 

[Rogue_Trader]

Hey Joe4,

Sorry for the long delay, I was out of town. I pretty much got everything up and running (minus a few minor details). Thanks again for the help! Saved me a lot of time.

 

[Joe4]

Great! Glad it worked out for you.