Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
I created a UDF to return the range/s of the visible cells in an AutoFiltered
range. When I use the function in VBA it returns the correct result with the range as an array of the visible cells like the following. A14:G16,A22:G22,A27:G28,A31:G31,A34:G34 When I use the function on a worksheet it simply returns a range that is the first and last cells of the unfiltered data in the entire Autofilter range including the non visible cells like the following. A2:G42 Is this just an idiosyncrasy of Excel or am I missing something here. Sub Test_FilterRnge() MsgBox FilterRnge(, 0) End Sub Function FilterRnge(Optional strWs As String = "", _ Optional bolAbs As Boolean = False) As String Dim ws As Worksheet Dim FilterVisible As Range If strWs = "" Then Set ws = ActiveSheet Else Set ws = Sheets(strWs) End If If ws.AutoFilterMode Then 'Test if filter arrows present If ws.FilterMode Then 'Test if actually filtered With ws.AutoFilter.Range 'Next line returns number of visible cells divided _ by number of columns in AutoFilter range. _ If greater than 1 then some data is visible. _ Equal to 1 then only column headers visible. If .SpecialCells(xlCellTypeVisible).Count / _ .Columns.Count 1 Then 'Assign the filtered range/s to a VBA variable _ No Column Headers; Data only. _ (Column numbers can be omitted in _ Offset and Resize functions.) Set FilterVisible = .Offset(1, 0) _ .Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible) End If End With End If End If If Not FilterVisible Is Nothing Then FilterRnge = FilterVisible.Address(bolAbs, bolAbs) Else FilterRnge = "Error!" End If End Function -- Regards, OssieMac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
Unfortunately SpecialCells does not work with a UDF called from a cell
formula. Look at the Hidden property of rows in the filter range. Regards, Peter T "OssieMac" wrote in message ... I created a UDF to return the range/s of the visible cells in an AutoFiltered range. When I use the function in VBA it returns the correct result with the range as an array of the visible cells like the following. A14:G16,A22:G22,A27:G28,A31:G31,A34:G34 When I use the function on a worksheet it simply returns a range that is the first and last cells of the unfiltered data in the entire Autofilter range including the non visible cells like the following. A2:G42 Is this just an idiosyncrasy of Excel or am I missing something here. Sub Test_FilterRnge() MsgBox FilterRnge(, 0) End Sub Function FilterRnge(Optional strWs As String = "", _ Optional bolAbs As Boolean = False) As String Dim ws As Worksheet Dim FilterVisible As Range If strWs = "" Then Set ws = ActiveSheet Else Set ws = Sheets(strWs) End If If ws.AutoFilterMode Then 'Test if filter arrows present If ws.FilterMode Then 'Test if actually filtered With ws.AutoFilter.Range 'Next line returns number of visible cells divided _ by number of columns in AutoFilter range. _ If greater than 1 then some data is visible. _ Equal to 1 then only column headers visible. If .SpecialCells(xlCellTypeVisible).Count / _ .Columns.Count 1 Then 'Assign the filtered range/s to a VBA variable _ No Column Headers; Data only. _ (Column numbers can be omitted in _ Offset and Resize functions.) Set FilterVisible = .Offset(1, 0) _ .Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible) End If End With End If End If If Not FilterVisible Is Nothing Then FilterRnge = FilterVisible.Address(bolAbs, bolAbs) Else FilterRnge = "Error!" End If End Function -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
Hi Peter,
I give up. Where do I find this or some documentation on what you are referring to? "Look at the Hidden property of rows in the filter range." -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
Look at this simple function:
Function see_able(inputt As Range) As String Dim rr As Range, r As Range see_able = "" Set rr = Intersect(inputt, ActiveSheet.UsedRange) For Each r In rr If Rows(r.Row).Hidden = False Then If see_able = "" Then see_able = r.Address Else see_able = see_able & "," & r.Address End If End If Next End Function It does not use SpecialCells. It just loops thru the cells in the input range and determines if they are in a visible row or not. So if rows 5 thru 7 are hidden, then: =see_able(C1:D11) will display: $C$1,$D$1,$C$2,$D$2,$C$3,$D$3,$C$4,$D$4,$C$8,$D$8, $C$9,$D$9,$C$10,$D$10,$C$11,$D$11 in the worksheet. -- Gary''s Student - gsnu200834 "OssieMac" wrote: Hi Peter, I give up. Where do I find this or some documentation on what you are referring to? "Look at the Hidden property of rows in the filter range." -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
Thanks to both of you for the answers. A picture is worth a 1000 words.
To Peter, I appreciate your taking the time to answer and the first part of your answer confirms to me that it is is just an idiosyncrasy of Excel. I completely misunderstood what you were referring to by "Look at the Hidden property of rows in the filter range". I thought that you meant that it would explain all about the first part of your answer and I couldn't make the connection. I am now assuming that Gary's Student correctly interpretted what you meant and it now makes sense. By the way at this point in time I don't need it as a worksheet function and can't even think of a use for it as a worksheet function at the moment. I only want the VBA to copy filtered results and decided to come up with something generic. I just had the function in a cell during testing. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
Try this and adapt to your needs -
Function VisFilterRows(Optional ws As Worksheet, _ Optional bUseAddress As Boolean) As String Dim n As Long Dim s As String, t1 As String, t2 As String Dim rFlt As Range Dim rw As Range On Error Resume Next If ws Is Nothing Then Set ws = Application.Caller.Parent On Error GoTo errH If ws Is Nothing Then Set ws = ActiveSheet Set rFlt = ws.AutoFilter.Range For Each rw In rFlt.Rows If Not rw.Hidden Then If bUseAddress Then t2 = rw.Address(0, 0) Else t2 = rw.Row End If If Len(t1) = 0 Then t1 = t2 End If ElseIf Len(t2) Then If Len(t1) Then If t1 < t2 Then t1 = t1 & ":" & t2 End If s = s & t1 & "," t1 = "": t2 = "" End If End If Next If Len(t1) Then If t1 < t2 Then t1 = t1 & ":" & t2 End If s = s & t1 & "," t1 = "": t2 = "" End If n = Len(s) If n Then s = Left$(s, n - 1) VisFilterRows = s Exit Function errH: VisFilterRows = "err:" & Err.Number End Function Regards, Peter T "OssieMac" wrote in message ... Hi Peter, I give up. Where do I find this or some documentation on what you are referring to? "Look at the Hidden property of rows in the filter range." -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns different result in worksheet to that in VBA
That demo is not quite right if using bUseAddress=True and the filter range
is 2+ columns. Easy to fix but as I doubt anyone will ever want it just remove the option altogether. Peter T "Peter T" <peter_t@discussions wrote in message ... Try this and adapt to your needs - Function VisFilterRows(Optional ws As Worksheet, _ Optional bUseAddress As Boolean) As String Dim n As Long Dim s As String, t1 As String, t2 As String Dim rFlt As Range Dim rw As Range On Error Resume Next If ws Is Nothing Then Set ws = Application.Caller.Parent On Error GoTo errH If ws Is Nothing Then Set ws = ActiveSheet Set rFlt = ws.AutoFilter.Range For Each rw In rFlt.Rows If Not rw.Hidden Then If bUseAddress Then t2 = rw.Address(0, 0) Else t2 = rw.Row End If If Len(t1) = 0 Then t1 = t2 End If ElseIf Len(t2) Then If Len(t1) Then If t1 < t2 Then t1 = t1 & ":" & t2 End If s = s & t1 & "," t1 = "": t2 = "" End If End If Next If Len(t1) Then If t1 < t2 Then t1 = t1 & ":" & t2 End If s = s & t1 & "," t1 = "": t2 = "" End If n = Len(s) If n Then s = Left$(s, n - 1) VisFilterRows = s Exit Function errH: VisFilterRows = "err:" & Err.Number End Function Regards, Peter T "OssieMac" wrote in message ... Hi Peter, I give up. Where do I find this or some documentation on what you are referring to? "Look at the Hidden property of rows in the filter range." -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DCount returns incorrect Result | Excel Worksheet Functions | |||
Cell returns blank result... | Excel Worksheet Functions | |||
result returns #N/B | Excel Discussion (Misc queries) | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
SUM returns wrong result | Excel Worksheet Functions |