Return a Range in a function

[mae0429]

I can't quite seem to understand what is happening with my code that causes a run-time error 91 (object variable or with block variable not set). The goal of the function is to basically find where the headings start and stop on the Excel sheet and then return that range of cells. Thank you for any help you may have!

Here is my code:


Option Explicit

Function HeaderRange() As Range
Dim endColumn, counter, i, j, k, startRow As Integer
Dim cellCheck, cellStart As String
' Get the number of headings
endColumn = 0
For i = 1 To 10
counter = 0
For j = 1 To 100
cellCheck = Cells(i, j)
If Not cellCheck = "" Then
counter = counter + 1
Else
Exit For
End If
Next j
If counter > endColumn Then
endColumn = counter - 1
End If
Next i
' Get the starting row number
For k = 1 To 100
cellStart = Cells(k, 2).Value
If Not cellStart = "" Then
startRow = k
Exit For
End If
Next k
HeaderRange = Range(Cells(startRow, 1), Cells(startRow, endColumn))
End Function

[Norie]

Well if you really wanted a range you would use the Set statement not just this.


HeaderRange = Range(Cells(startRow, 1), Cells(startRow, endColumn))

ie


Set HeaderRange = Range(Cells(startRow, 1), Cells(startRow, endColumn))

But I have ask why you are using this approach in the first place.

[mae0429]

Thank you for the advice. I'm using this because the headings are not always in the same place on the file that is being imported (older versions of the data have less room at the top then the new versions do, and some versions have extra columns. Is there an easier way to do this universally? If not, let me know and I'll mark this thread as solved.

[xneurosis]

Thanks!