Custom function will not select visible rows only

  • Hello,


    I've been looking around for a few days now for a solution to my fine little problem. I want to pass a range into a function that then checks to see if there is any data present beneath it. If there is data the function will output the range that includes the headers as well as the visible data. As well if there is no visible data then the original range (headers) will be output.


    I've done a few different iterations but this seems to get me the closest so far:


    [VBA]
    Function ChkRng(crng As Range, c As Integer, k As String) As Range


    Dim i As Range


    Set i = Range(crng, crng.End(xlDown))


    i.AutoFilter field:=c, Criteria1:=k


    Set ChkRng = i.SpecialCells(xlCellTypeVisible)


    End Function[/VBA]



    But this outputs all the data to the last visible data row, including the hidden rows...


    I've been trying other options as well such as using a table (doesn't work with my overall workbook as a table), as well as trying to pass autofilter criteria into the function and trying to do the filtering there as well. nothing seems to be working.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!