This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.
Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("C7") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub