Function to Return Non-empty Range

[animas]

Public Function DataRange(iRange As Range) 
    DataRange = iRange.Cells(1).Address & ":" & iRange.End(xlDown).Address
End Function

above function returns the range as string. How do i return this string as range?

 

[mikerickson]

Try

Public Function DataRange(iRange As Range) As Range
    With iRange
        Set DataRange = Range(.Cells(1), .End(xlDown))
    End With
End Function

 

[animas]

that returns the second cell value.

 

[mikerickson]

My typo, try this

Public Function DataRange(iRange As Range) As Range
    With iRange
        Set DataRange = Range(.Cells(1,1), .End(xlDown))
    End With
End Function

Also, what are you trying to do? Using xlDown, in any of these configurations will not return a discontinuous range.

Given a range, what range is this function supposed to return?

 

[animas]

Now it's returning 4th cell value. I will supply a range which will have empty cells too. It will return the range of first set of cells which doesn't have empty values.