Not clear what YearLen represents?, or whether StartPLace is a "fixed" cell or column? So made this to generate random ranges.
Click button and Messagebox tells you which columns are covered in the Ranges. You need only one message because you set both your ranges to start and end with the same columns, only differing in the row at which they start.
Option Explicit
Dim EndCol As Integer, FirstCol As Integer, lrow As Integer, StartPlace As Integer, StartCol As Integer, YearLen As Integer
Dim MatrixRange1 As Range, MatrixRange2 As Range
Sub Matrix2()
lrow = Cells(Rows.Count, StartPlace + 1).End(xlUp).Row
If lrow < 3 Then lrow = 3
YearLen = WorksheetFunction.RandBetween(2, lrow)
StartPlace = WorksheetFunction.RandBetween(1, 20)
StartCol = StartPlace + 5
EndCol = StartCol + YearLen
If EndCol > 26 Then EndCol = 26
With Sheets("Sheet1")
Set MatrixRange1 = .Range(.Cells(1, StartCol), .Cells(1, EndCol - 2))
MsgBox "Ranges start at Column " & .Cells(1, StartCol) & " to Column " & .Cells(1, EndCol-2)
Set MatrixRange2 = .Range(.Cells(3, StartCol), .Cells(1, EndCol - 2))
End With
End Sub
NOTE: Just realised attached sample hasn't offset the End Col, so you need to change the Set Matrix rows on it from ".Cells(1, EndCol)" to ".Cells(1, EndCol - 2)"
Hope this helps
Ochimus
Bookmarks