Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Hi All,
2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Second question First: the test of .Hidden for a range that includes several
rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
J -
There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since ..Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
The Find function works fine locating text in hidden cells in my tests.
Maybe you are getting tripped up by the settings used on previous searches you did. Find has an annoying (well, at least to me) feature whereby it remembers the settings from the last time it was used. So, if you set it to look at the whole cell's content and now you are trying to find a word within the text of a cell, it won't find it because the setting from last time told it to look at the whole word. It is always a good idea to specify the optional arguments to the Find function every time you use it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... J - There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since .Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Hi J,
You were so helpful I thought you might like to see the almost finished function. I'm going to test whether scanning a row range is quicker than If rows(row).Hidden Then ... BTW, the code works, I've not yet found any problems with it. On an older computer with a 386 chip, 5000 rows with 10 of them hidden took .17 seconds. The code is self-contained, I've replaced some of my other function calls with GoSub's. (and hard coded MsoMaxRow and MSoMaxCol values.) Thanks again. Neal Function HidnQtyF(ByVal Ws As Worksheet, bRowNum As Boolean, _ FMnumOrRng As Variant, TOnum As Long, Status As String, _ Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExitFirHidn As Boolean = False, _ Optional INnumQty As Long = 0) As Long 'Outputs: Return count of hidden rows Xor columns, bRowNum input, ' True=scan for hidden rows, F= for columns. ' Optional array of hidden row/column numbers or a range bWantOutput parm. ' If bWantOutput, the input OutAyOrRng parm type determines type of output. ' An unallocated array() or a range object. Ay output is base 1. ' Neither raises error 13, type mismatch. ' INnumQty is the input count of rows or columns being scanned, see bRowNum. ' 'Other Inputs: Ws sheet, if Nothing, Active is assumed. ' FMnumOrRng, a) Input range(Ws is set to .Parent) Areas.Count 1 is OK. ' b) From row or column #. ' c) Neither raises error 13 type mismatch. ' Tonum, the 'To' row/column # when FMnumOrRng is numeric. Lower to Higher ' or vice-versa is OK for scanning row or column #'s. ' bExitFirHidn, False, scan all input. True, Proc quits after 1st hidden row or ' col is found. Output has only first item. Function return value will be 1. 'lockdown devel Dim b1DimOut As Boolean Dim bRangeIn As Boolean 'T= range input rather than FM and TO nums. Dim RCAdr As String 'row(s) or column(s) string address Dim Aix As Long 'area index Dim Col1 As Long Dim Col2 As Long Dim FMnum As Long Dim HiddenQty As Long Dim InnerRC As Long 'inner loop row or column number Dim MiscNum As Long 'miscell. Const MSoMaxCol = 256 'todo, function, update values Excel 10,11,etc. Const MSoMaxRow = 65536 Dim Qty As Long 'miscell. Dim RC As Long 'row or column number Dim ScanQty As Long 'row/col count in an area Dim StepVal As Long 'up/down right/left row/col loops 'mainline start Status = "" INnumQty = 0 If IsNumeric(FMnumOrRng) Then 'f What's the input ? If Ws Is Nothing Then Set Ws = ActiveSheet ElseIf IsObject(FMnumOrRng) Then If TypeName(FMnumOrRng) = "Nothing" Then Status = "Warning, HidnQtyF, FMnumOrRng Input = Nothing" Exit Function 'zip to scan End If If TypeName(FMnumOrRng) = "Range" Then bRangeIn = True Set Ws = FMnumOrRng.Parent Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not Rng Obj" Err.Raise 13 End If Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not# Not Rng" Err.Raise 13 End If With Ws 'end with @ end sub If Not bRangeIn Then '1 range, with from and to inputs. FMnum = FMnumOrRng If FMnum < 1 Then FMnum = 1 If TOnum < 1 Then TOnum = 1 If bRowNum Then If FMnum MSoMaxRow Then FMnum = MSoMaxRow If TOnum MSoMaxRow Then TOnum = MSoMaxRow Else If FMnum MSoMaxCol Then FMnum = MSoMaxCol If TOnum MSoMaxCol Then TOnum = MSoMaxCol End If INnumQty = Abs(TOnum - FMnum) + 1 If FMnum <= TOnum Then StepVal = 1 Else StepVal = -1 If bWantOutput Then ScanQty = INnumQty GoSub AllocateOutP End If GoSub A_Ws_Scan 'f Main Loops ElseIf bRowNum Then 'range input, 1 or more areas For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Row TOnum = FMnum + FMnumOrRng.Areas(Aix).Rows.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix Else For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Column TOnum = FMnum + FMnumOrRng.Areas(Aix).Columns.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix End If If b1DimOut And HiddenQty 0 Then ReDim Preserve _ OutAyOrRng(1 To HiddenQty) HidnQtyF = HiddenQty 'mainline end Exit Function A_Ws_Scan: 'Scan rows or columns, count, write outputs per function parms. ScanQty = Abs(TOnum - FMnum) + 1 If bRangeIn Then INnumQty = INnumQty + ScanQty If Not bWantOutput Then 'count only. If bRowNum Then For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC Else For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC End If ElseIf bRowNum Then 'Scan, write outputs, hidden rows If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then If Not bExitFirHidn Then 'Scan all for hidden 'til input rows end. If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC 'f updated array Else InnerRC = RC 'f update range when contiguous hidden's end. Do While .Rows(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 RCAdr = RC & ":" & InnerRC GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else RCAdr = RC GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC Else 'Scan, write outputs, hidden columns If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then If Not bExitFirHidn Then If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC Else InnerRC = RC Do While .Columns(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 'RCAdr = ColRngAdrF(RC, InnerRC) '$A:$B from col #'s Col1 = RC Col2 = InnerRC GoSub ComposeColAdr GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else 'RCAdr = ColRngAdrF(RC, RC) Col1 = RC Col2 = RC GoSub ComposeColAdr GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC End If Return AddToOutRange: 'Set with Union or not If bRowNum Then If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Rows(RCAdr)) Else Set OutAyOrRng = .Rows(RCAdr) End If Else If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Columns(RCAdr)) Else Set OutAyOrRng = .Columns(RCAdr) End If End If Return AllocateOutP: 'Dim/ReDim array to hold row or column #'s, Init Rng output. If bRangeIn Then 'init on area 1, then redim array for +1 areas If Aix = 1 Then GoSub AllocateAy1st ElseIf b1DimOut Then 'Be able to hold all items about to be scanned. MiscNum = UBound(OutAyOrRng) - HiddenQty 'available elements If ScanQty MiscNum Then ReDim Preserve OutAyOrRng(1 To (UBound(OutAyOrRng) _ + (ScanQty - MiscNum))) End If End If Else 'one range of row or column #'s GoSub AllocateAy1st End If Return AllocateAy1st: 'First initialization If IsObject(OutAyOrRng) And (TypeName(OutAyOrRng) = "Nothing" Or _ TypeName(OutAyOrRng) = "Range") Then Set OutAyOrRng = Nothing Else 'f Erase when more than 1 dimen, re-dim If Not IsArray(OutAyOrRng) Then Status = "Tech Error, HidnQtyF, Input OutAyOrRng, Not Rng Not Ay" Err.Raise 13 End If MiscNum = 0 Do MiscNum = MiscNum + 1 On Error Resume Next Qty = UBound(OutAyOrRng, MiscNum) Loop Until Err.Number < 0 On Error GoTo 0 Qty = Qty - 1 'dimen of Ay If 1 < Qty Then Erase OutAyOrRng b1DimOut = True ReDim OutAyOrRng(1 To ScanQty) End If Return ComposeColAdr: 'string address via two col #'s RCAdr = Range(Cells(1, Col1), Cells(1, Col2)).Address 'f $E$1:$F$1 RCAdr = Replace(RCAdr, "$1", "") 'f $E:$F Return End With 'With Ws at main top End Function -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Hi Rick,
Thanks for the post, but respectfully, I disagree with your statement below with to me, an important qualification. "The Find function works fine locating text in hidden cells in my tests." I agree, IF the text does NOT result from a formula. If the text is a constant so that .Value is the same as .Formula, .Find works great. IF however, the .Value < .Formula property for the cell, .Find will not find it if the cell is hidden. At least my tests prove this. (I've had a pretty long thread on this board with others who explained it to me.) The reason for my writing a hidden row or column function that kicked off this thread, is that I have several utility procs that use .Find in them. If I'm looking for .Value, and "I" don't know if rows are columns are hidden, I want the capability of un-hiding the rows and columns before executing the .Find. I've put all of the code for my HidnQtyF function in a reply to JLatham in this thread. I'm completing the testing for it, but so far so good. If you'd like to give it a spin, I'd appreciate any comments. Any yes, I agree it's a pain in the ass that .Find remembers the prior settings, But I can see MSoft's point of view when using Cntl + F to find the next values. I don't know a whole lot about custom dialog boxes, but there "oughta be a way" for it to provide the attributes of a find, but have the code reset to the defaults each time it executes. Thanks for the post, I appreciate very much all of the time you guys spend on this board, Regards, Neal -- Neal Z "Rick Rothstein" wrote: The Find function works fine locating text in hidden cells in my tests. Maybe you are getting tripped up by the settings used on previous searches you did. Find has an annoying (well, at least to me) feature whereby it remembers the settings from the last time it was used. So, if you set it to look at the whole cell's content and now you are trying to find a word within the text of a cell, it won't find it because the setting from last time told it to look at the whole word. It is always a good idea to specify the optional arguments to the Find function every time you use it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... J - There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since .Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
IF however, the .Value < .Formula property for the cell, .Find will not
find it if the cell is hidden. At least my tests prove this. (I've had a pretty long thread on this board with others who explained it to me.) I am probably misunderstanding what the actual problem is, but to the best of my knowledge and experience, Find can find text in a cell whether that text is displayed in the cell as a constant or as the result of a formula OR if that text is inside the formula itself and whether the cell is hidden or not does not matter. If you have an example where this is not true, then if you would please send it to me (remove the NO.SPAM stuff from my address) with a brief description of what is not being found and where it is located at, then I would be happy to look at it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... Hi Rick, Thanks for the post, but respectfully, I disagree with your statement below with to me, an important qualification. "The Find function works fine locating text in hidden cells in my tests." I agree, IF the text does NOT result from a formula. If the text is a constant so that .Value is the same as .Formula, .Find works great. IF however, the .Value < .Formula property for the cell, .Find will not find it if the cell is hidden. At least my tests prove this. (I've had a pretty long thread on this board with others who explained it to me.) The reason for my writing a hidden row or column function that kicked off this thread, is that I have several utility procs that use .Find in them. If I'm looking for .Value, and "I" don't know if rows are columns are hidden, I want the capability of un-hiding the rows and columns before executing the .Find. I've put all of the code for my HidnQtyF function in a reply to JLatham in this thread. I'm completing the testing for it, but so far so good. If you'd like to give it a spin, I'd appreciate any comments. Any yes, I agree it's a pain in the ass that .Find remembers the prior settings, But I can see MSoft's point of view when using Cntl + F to find the next values. I don't know a whole lot about custom dialog boxes, but there "oughta be a way" for it to provide the attributes of a find, but have the code reset to the defaults each time it executes. Thanks for the post, I appreciate very much all of the time you guys spend on this board, Regards, Neal -- Neal Z "Rick Rothstein" wrote: The Find function works fine locating text in hidden cells in my tests. Maybe you are getting tripped up by the settings used on previous searches you did. Find has an annoying (well, at least to me) feature whereby it remembers the settings from the last time it was used. So, if you set it to look at the whole cell's content and now you are trying to find a word within the text of a cell, it won't find it because the setting from last time told it to look at the whole word. It is always a good idea to specify the optional arguments to the Find function every time you use it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... J - There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since .Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Rick,
Dammit, I was wrong, but I was right too. .Find will not find a value in a hidden cell even if it's a constant. I put "word" in a cell and hid the row. from the immediate window debug.print activesheet.cells.find("word",,xlvalues,xlpart).ad dress you'll get a 91 error as the cell was not found, so it can't print the address now try debug.print activesheet.cells.find("word",,xlformulas,xlpart). address and you'll get the cell address. Neal Z. -- Neal Z "Rick Rothstein" wrote: The Find function works fine locating text in hidden cells in my tests. Maybe you are getting tripped up by the settings used on previous searches you did. Find has an annoying (well, at least to me) feature whereby it remembers the settings from the last time it was used. So, if you set it to look at the whole cell's content and now you are trying to find a word within the text of a cell, it won't find it because the setting from last time told it to look at the whole word. It is always a good idea to specify the optional arguments to the Find function every time you use it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... J - There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since .Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
I have looked over your code and I have a feeling it can be simplified
somewhat, but I am not sure of that because I don't fully understand the ultimate purpose of the function. Can you show us some examples of various worksheet setups along with the possible input conditions (function arguments) and the expected outputs for them? Just a quick observation on one section of your code. If I understand it correctly, I believe this section... ComposeColAdr: 'string address via two col #'s RCAdr = Range(Cells(1, Col1), Cells(1, Col2)).Address 'f $E$1:$F$1 RCAdr = Replace(RCAdr, "$1", "") 'f $E:$F Return can be replaced with this... ComposeColAdr: 'string address via two col #'s RCAdr = Range(Columns(Col1), Columns(Col2)).Address 'f $E:$F Return which will work if Col1 and Col2 are either numbers or letters. Since you have Dim'med Col1 and Col2 as Long, you can also use this alternative code as well... ComposeColAdr: 'string address via two col #'s RCAdr = Columns(Col1).Resize(, Col2 - Col1 + 1).Address 'f $E:$F Return -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... Hi J, You were so helpful I thought you might like to see the almost finished function. I'm going to test whether scanning a row range is quicker than If rows(row).Hidden Then ... BTW, the code works, I've not yet found any problems with it. On an older computer with a 386 chip, 5000 rows with 10 of them hidden took .17 seconds. The code is self-contained, I've replaced some of my other function calls with GoSub's. (and hard coded MsoMaxRow and MSoMaxCol values.) Thanks again. Neal Function HidnQtyF(ByVal Ws As Worksheet, bRowNum As Boolean, _ FMnumOrRng As Variant, TOnum As Long, Status As String, _ Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExitFirHidn As Boolean = False, _ Optional INnumQty As Long = 0) As Long 'Outputs: Return count of hidden rows Xor columns, bRowNum input, ' True=scan for hidden rows, F= for columns. ' Optional array of hidden row/column numbers or a range bWantOutput parm. ' If bWantOutput, the input OutAyOrRng parm type determines type of output. ' An unallocated array() or a range object. Ay output is base 1. ' Neither raises error 13, type mismatch. ' INnumQty is the input count of rows or columns being scanned, see bRowNum. ' 'Other Inputs: Ws sheet, if Nothing, Active is assumed. ' FMnumOrRng, a) Input range(Ws is set to .Parent) Areas.Count 1 is OK. ' b) From row or column #. ' c) Neither raises error 13 type mismatch. ' Tonum, the 'To' row/column # when FMnumOrRng is numeric. Lower to Higher ' or vice-versa is OK for scanning row or column #'s. ' bExitFirHidn, False, scan all input. True, Proc quits after 1st hidden row or ' col is found. Output has only first item. Function return value will be 1. 'lockdown devel Dim b1DimOut As Boolean Dim bRangeIn As Boolean 'T= range input rather than FM and TO nums. Dim RCAdr As String 'row(s) or column(s) string address Dim Aix As Long 'area index Dim Col1 As Long Dim Col2 As Long Dim FMnum As Long Dim HiddenQty As Long Dim InnerRC As Long 'inner loop row or column number Dim MiscNum As Long 'miscell. Const MSoMaxCol = 256 'todo, function, update values Excel 10,11,etc. Const MSoMaxRow = 65536 Dim Qty As Long 'miscell. Dim RC As Long 'row or column number Dim ScanQty As Long 'row/col count in an area Dim StepVal As Long 'up/down right/left row/col loops 'mainline start Status = "" INnumQty = 0 If IsNumeric(FMnumOrRng) Then 'f What's the input ? If Ws Is Nothing Then Set Ws = ActiveSheet ElseIf IsObject(FMnumOrRng) Then If TypeName(FMnumOrRng) = "Nothing" Then Status = "Warning, HidnQtyF, FMnumOrRng Input = Nothing" Exit Function 'zip to scan End If If TypeName(FMnumOrRng) = "Range" Then bRangeIn = True Set Ws = FMnumOrRng.Parent Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not Rng Obj" Err.Raise 13 End If Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not# Not Rng" Err.Raise 13 End If With Ws 'end with @ end sub If Not bRangeIn Then '1 range, with from and to inputs. FMnum = FMnumOrRng If FMnum < 1 Then FMnum = 1 If TOnum < 1 Then TOnum = 1 If bRowNum Then If FMnum MSoMaxRow Then FMnum = MSoMaxRow If TOnum MSoMaxRow Then TOnum = MSoMaxRow Else If FMnum MSoMaxCol Then FMnum = MSoMaxCol If TOnum MSoMaxCol Then TOnum = MSoMaxCol End If INnumQty = Abs(TOnum - FMnum) + 1 If FMnum <= TOnum Then StepVal = 1 Else StepVal = -1 If bWantOutput Then ScanQty = INnumQty GoSub AllocateOutP End If GoSub A_Ws_Scan 'f Main Loops ElseIf bRowNum Then 'range input, 1 or more areas For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Row TOnum = FMnum + FMnumOrRng.Areas(Aix).Rows.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix Else For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Column TOnum = FMnum + FMnumOrRng.Areas(Aix).Columns.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix End If If b1DimOut And HiddenQty 0 Then ReDim Preserve _ OutAyOrRng(1 To HiddenQty) HidnQtyF = HiddenQty 'mainline end Exit Function A_Ws_Scan: 'Scan rows or columns, count, write outputs per function parms. ScanQty = Abs(TOnum - FMnum) + 1 If bRangeIn Then INnumQty = INnumQty + ScanQty If Not bWantOutput Then 'count only. If bRowNum Then For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC Else For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC End If ElseIf bRowNum Then 'Scan, write outputs, hidden rows If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then If Not bExitFirHidn Then 'Scan all for hidden 'til input rows end. If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC 'f updated array Else InnerRC = RC 'f update range when contiguous hidden's end. Do While .Rows(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 RCAdr = RC & ":" & InnerRC GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else RCAdr = RC GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC Else 'Scan, write outputs, hidden columns If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then If Not bExitFirHidn Then If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC Else InnerRC = RC Do While .Columns(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 'RCAdr = ColRngAdrF(RC, InnerRC) '$A:$B from col #'s Col1 = RC Col2 = InnerRC GoSub ComposeColAdr GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else 'RCAdr = ColRngAdrF(RC, RC) Col1 = RC Col2 = RC GoSub ComposeColAdr GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC End If Return AddToOutRange: 'Set with Union or not If bRowNum Then If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Rows(RCAdr)) Else Set OutAyOrRng = .Rows(RCAdr) End If Else If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Columns(RCAdr)) Else Set OutAyOrRng = .Columns(RCAdr) End If End If Return AllocateOutP: 'Dim/ReDim array to hold row or column #'s, Init Rng output. If bRangeIn Then 'init on area 1, then redim array for +1 areas If Aix = 1 Then GoSub AllocateAy1st ElseIf b1DimOut Then 'Be able to hold all items about to be scanned. MiscNum = UBound(OutAyOrRng) - HiddenQty 'available elements If ScanQty MiscNum Then ReDim Preserve OutAyOrRng(1 To (UBound(OutAyOrRng) _ + (ScanQty - MiscNum))) End If End If Else 'one range of row or column #'s GoSub AllocateAy1st End If Return AllocateAy1st: 'First initialization If IsObject(OutAyOrRng) And (TypeName(OutAyOrRng) = "Nothing" Or _ TypeName(OutAyOrRng) = "Range") Then Set OutAyOrRng = Nothing Else 'f Erase when more than 1 dimen, re-dim If Not IsArray(OutAyOrRng) Then Status = "Tech Error, HidnQtyF, Input OutAyOrRng, Not Rng Not Ay" Err.Raise 13 End If MiscNum = 0 Do MiscNum = MiscNum + 1 On Error Resume Next Qty = UBound(OutAyOrRng, MiscNum) Loop Until Err.Number < 0 On Error GoTo 0 Qty = Qty - 1 'dimen of Ay If 1 < Qty Then Erase OutAyOrRng b1DimOut = True ReDim OutAyOrRng(1 To ScanQty) End If Return ComposeColAdr: 'string address via two col #'s RCAdr = Range(Cells(1, Col1), Cells(1, Col2)).Address 'f $E$1:$F$1 RCAdr = Replace(RCAdr, "$1", "") 'f $E:$F Return End With 'With Ws at main top End Function -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Correct... this is what I was alluding to when I said "It is always a good
idea to specify the optional arguments to the Find function every time you use it."... the settings for each argument guide the Find function as to what it will find irrespective of the hidden state of the cells being searched. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... Rick, Dammit, I was wrong, but I was right too. .Find will not find a value in a hidden cell even if it's a constant. I put "word" in a cell and hid the row. from the immediate window debug.print activesheet.cells.find("word",,xlvalues,xlpart).ad dress you'll get a 91 error as the cell was not found, so it can't print the address now try debug.print activesheet.cells.find("word",,xlformulas,xlpart). address and you'll get the cell address. Neal Z. -- Neal Z "Rick Rothstein" wrote: The Find function works fine locating text in hidden cells in my tests. Maybe you are getting tripped up by the settings used on previous searches you did. Find has an annoying (well, at least to me) feature whereby it remembers the settings from the last time it was used. So, if you set it to look at the whole cell's content and now you are trying to find a word within the text of a cell, it won't find it because the setting from last time told it to look at the whole word. It is always a good idea to specify the optional arguments to the Find function every time you use it. -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... J - There's one last question this thread @ then end of this post. Thanks. Further testing after I made the post revealed to me that it's a 'first row' in the range problem, I had thought it might be about only row 1. The function I'm building is part of a .Find 'utility' function since .Find won't find values in cells that are hidden, so I need to test for "hiddens" and un-hide them before the .Find. and then re-hide them as needed. I'll incorporate your ideas with one addition. The size of the row range I have to check is about 3000 rows and in the code I didn't show there's an Areas.Count layer since there may be non-contiguous groups of hidden rows. Would you say 3000 rows is on the small or medium size ? (Excel 2007 changed the max row game) Last question: you can save me some testing time Is it more efficient, i.e. less execution time, to use For Each "Something" in ARange more code here Next "Something" versus, (when it comes to entire rows or columns) For Row = Fromrow To ToRow Step StepVariable If Rows(Row).Hidden = whatever then .... Next Row My reason for the row loop is it gives me the flexibility of going from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not figured out a way to make For Each go backwards within 1 Area. You're kinda stuck with upper left to lower right, huh ? Since I'm building a Hidden "utility" proc, I wanted forwards and backwards capability. Thanks again, Neal Z. ] -- Neal Z "JLatham" wrote: Second question First: the test of .Hidden for a range that includes several rows will return the state of the first row in the range. With a range that includes rows 1:10 and row 1 hidden, then it would return True. But if row 1 was visible and even all 9 other rows were hidden, it would return False. So that results in the answer to your first question being pretty my "Yes" you have to test each one individually as far as I know. For the best performance, especially on a very large number of rows, you should use Range objects to test. Some Examples: We will assume that two variables are set up to hold the row numbers involved: firstRow and lastRow Dim testRows As Range Dim anyRow As Range Dim allHiddenFlag As Boolean Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden allHiddenFlag = True For Each anyRow In testRows If anyRow.Hidden = False Then allHiddenFlag=False Exit For End If Next 'this setup would return the row number of the first hidden row 'in the range Dim testRows As Range Dim anyRow As Range Dim firstHiddenRow As Long Set testRows = Rows(firstRow & ":" & lastRow) 'set default to assume all are hidden firstHiddenRow = 0 For Each anyRow In testRows If anyRow.Hidden = trueThen firstHiddenRow = anyRow.Row Exit For End If Next 'test firstHiddenRow for non-zero "Neal Zimm" wrote: Hi All, 2 Questions follow, This seems simple, but I'm not seeing it. Below #2 is an excerpt from a bigger function. 1. Is there a quicker way to find rows that are hidden within a larger row range, or is this about the only way? for row = x to y if rows(row).hidden then ..... next row I looked at .Find with the formats it can find but .Hidden is a property, so , no go. 2. I do not understand why the If stmt below evaluated true when all of rows 1 thru 10, inclusive, were not hidden. Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _ TOrow As Long, Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExit1stHidn As Boolean = False, _ Optional INrowsQty As Long = 0) As Long With Ws INrowsQty = Abs(TOrow - FMrow) + 1 'FMrow was 10, TOrow was 1 ' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden. If .Rows(FMrow & ":" & TOrow).Hidden = True Then 'more code here, 'don't all of them have to be hidden for "True" End if End With 'more code here to value the function End Function Thanks -- Neal Z . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing/Finding .Hidden for Rows May21
Rick,
This post is almost surely way too long, but I think a full explanation is in order. I have just now realized how tough a job you guys have in answering our questions because you are not mind readers and cannot really know our level of knowledge. A friend of mine uses the acronym of COIC for this phenomenon. COIC: Clear, Only If Known. (i.e. it's clear to me, 'cuz I'm writing it and I know all the background, LOL) There are also times when my screwy brain interprets what you guys say in the wrong way. I have a flaw(one among many) where I take things way too literally. That said, ..... I'm combining your last two posts in this thread along some of the prior's. They are not in strict chronological sequence. Your offer to have me send you more complete examples is kind, but I don't think I'll need to. I am self taught in VBA and decently knowledgeable in some stuff, but the rawest of rookies in others. This is the "problem" that kicked off this entire thread. The data was rows 1 and 10 were hidden, 2-9 not hidden. If Rows("1:10").Hidden = True Then ...... To me, it's just stupid, as Latham explained that the state of the first row in the range is returned. (God help me, it 'should' be False since all of the rows were not hidden, but I never knew, nor came across any reference to the DIFFERENCE between TESTING a range, and USING a range to set all of its values, properties, to "something". So, I wrote the HidnQtyF function. It's objective is to return a count of hidden rows (or columns) in the range specified in the arguments, as well as returning the range of what's actually hidden so the calling proc can unhide them, and as needed, re-hide them. The reason for this is that there are sheets in my App that are protected but cannot be password protected. (You'll have to take this on faith for now) If a user hides stuff that "shouldn't" be hidden the code can have a problem since I try to find stuff in rows or columns. We argued a bit about whether or not .Find can find stuff in hidden cells. Your words: "The Find function works fine locating text in hidden cells in my tests." .... "irrespective of the hidden state of the cells being searched." This is what you were ALLUDING to (my emphasis) when you spoke about .Find remembering the prior use's settings. Yet, you said "Correct" to this example: debug.print activesheet.cells.find("word",,xlvalues,xlpart).ad dress The above failed when the word was in a hidden row. My brain works like this: - the value existed - it was in a hidden row - .Find did not find it using xlvalues - .Find found it with xlformulas - The hidden state is not "irrespective" - If it's hidden, and you want to find it, unhide it or use xlFormulas. - To me, this is not a .Find prior setting issue, it's a correct parm issue but there's no way you could know that, and that I knew the difference between xlValues and xlFormulas as optional parms in .Find. Honest to gosh I'm not trying to prove anyone is "wrong", but your phrase "The Find function works fine locating text in hidden cells in my tests." maybe coulda have had "If you look for it in the right way." at the end of the phrase. We agree on the facts, we can agree to disagree on the language (LOL, English not vba) your comment on the ComposeColAdr: paragraph. I wanted the posted HidnQtyF code to be self contained, so I rephrased another function and turned it into the paragraph. I think I know that the .Columns(????) vba syntax can be: Set RangeName = Ws.Columns(Stuff) Stuff can be: a column #, column letter(s), or "letter(s) & ":" & letter(s)" UNlike .Rows(Stuff) it cannot be "1:10" for columns A:J, as I got a runtime error on that. I use the function when the calling proc does not use column letter variables, but only column number variables and I need the Stuff for a contiguous column range. (When calling proc has the letters there's no need to call the function.) The complete function is at the end of this post. I'll try out your .Resize usage to see if it's quicker than what's below. Again, Many Thanks for your time on this, Neal Z Function ColRngAdrF(FMcol As Long, TOcol As Long, _ Optional ColRngId As String = "") As String ' Return an absolute column range address string via numeric from/to 'column numbers. Optional Id return of address without $. 'Invalid column numbers input defaults to minimum and maximum columns. 'lockdown,09/17/2008, 'renamed from ColRngStrF 'todo crit, need test MsoMaxCol Excel version, 256 versus ??? Dim Text As String 'mainline start If FMcol < 1 Then FMcol = 1 ElseIf FMcol MSoMaxCol Then FMcol = MSoMaxCol End If If TOcol < 1 Then TOcol = 1 ElseIf TOcol MSoMaxCol Then TOcol = MSoMaxCol End If Text = Range(Cells(1, FMcol), Cells(1, TOcol)).Address 'get $E$1:$F$1 Text = Replace(Text, "$1", "") 'strip row out get $E:$F ColRngAdrF = Text ColRngId = Replace(Text, "$", "") 'strip $, get E:F 'mainline end End Function -- Neal Z "Rick Rothstein" wrote: I have looked over your code and I have a feeling it can be simplified somewhat, but I am not sure of that because I don't fully understand the ultimate purpose of the function. Can you show us some examples of various worksheet setups along with the possible input conditions (function arguments) and the expected outputs for them? Just a quick observation on one section of your code. If I understand it correctly, I believe this section... ComposeColAdr: 'string address via two col #'s RCAdr = Range(Cells(1, Col1), Cells(1, Col2)).Address 'f $E$1:$F$1 RCAdr = Replace(RCAdr, "$1", "") 'f $E:$F Return can be replaced with this... ComposeColAdr: 'string address via two col #'s RCAdr = Range(Columns(Col1), Columns(Col2)).Address 'f $E:$F Return which will work if Col1 and Col2 are either numbers or letters. Since you have Dim'med Col1 and Col2 as Long, you can also use this alternative code as well... ComposeColAdr: 'string address via two col #'s RCAdr = Columns(Col1).Resize(, Col2 - Col1 + 1).Address 'f $E:$F Return -- Rick (MVP - Excel) "Neal Zimm" wrote in message ... Hi J, You were so helpful I thought you might like to see the almost finished function. I'm going to test whether scanning a row range is quicker than If rows(row).Hidden Then ... BTW, the code works, I've not yet found any problems with it. On an older computer with a 386 chip, 5000 rows with 10 of them hidden took .17 seconds. The code is self-contained, I've replaced some of my other function calls with GoSub's. (and hard coded MsoMaxRow and MSoMaxCol values.) Thanks again. Neal Function HidnQtyF(ByVal Ws As Worksheet, bRowNum As Boolean, _ FMnumOrRng As Variant, TOnum As Long, Status As String, _ Optional bWantOutput As Boolean = False, _ Optional OutAyOrRng As Variant = "", _ Optional bExitFirHidn As Boolean = False, _ Optional INnumQty As Long = 0) As Long 'Outputs: Return count of hidden rows Xor columns, bRowNum input, ' True=scan for hidden rows, F= for columns. ' Optional array of hidden row/column numbers or a range bWantOutput parm. ' If bWantOutput, the input OutAyOrRng parm type determines type of output. ' An unallocated array() or a range object. Ay output is base 1. ' Neither raises error 13, type mismatch. ' INnumQty is the input count of rows or columns being scanned, see bRowNum. ' 'Other Inputs: Ws sheet, if Nothing, Active is assumed. ' FMnumOrRng, a) Input range(Ws is set to .Parent) Areas.Count 1 is OK. ' b) From row or column #. ' c) Neither raises error 13 type mismatch. ' Tonum, the 'To' row/column # when FMnumOrRng is numeric. Lower to Higher ' or vice-versa is OK for scanning row or column #'s. ' bExitFirHidn, False, scan all input. True, Proc quits after 1st hidden row or ' col is found. Output has only first item. Function return value will be 1. 'lockdown devel Dim b1DimOut As Boolean Dim bRangeIn As Boolean 'T= range input rather than FM and TO nums. Dim RCAdr As String 'row(s) or column(s) string address Dim Aix As Long 'area index Dim Col1 As Long Dim Col2 As Long Dim FMnum As Long Dim HiddenQty As Long Dim InnerRC As Long 'inner loop row or column number Dim MiscNum As Long 'miscell. Const MSoMaxCol = 256 'todo, function, update values Excel 10,11,etc. Const MSoMaxRow = 65536 Dim Qty As Long 'miscell. Dim RC As Long 'row or column number Dim ScanQty As Long 'row/col count in an area Dim StepVal As Long 'up/down right/left row/col loops 'mainline start Status = "" INnumQty = 0 If IsNumeric(FMnumOrRng) Then 'f What's the input ? If Ws Is Nothing Then Set Ws = ActiveSheet ElseIf IsObject(FMnumOrRng) Then If TypeName(FMnumOrRng) = "Nothing" Then Status = "Warning, HidnQtyF, FMnumOrRng Input = Nothing" Exit Function 'zip to scan End If If TypeName(FMnumOrRng) = "Range" Then bRangeIn = True Set Ws = FMnumOrRng.Parent Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not Rng Obj" Err.Raise 13 End If Else Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not# Not Rng" Err.Raise 13 End If With Ws 'end with @ end sub If Not bRangeIn Then '1 range, with from and to inputs. FMnum = FMnumOrRng If FMnum < 1 Then FMnum = 1 If TOnum < 1 Then TOnum = 1 If bRowNum Then If FMnum MSoMaxRow Then FMnum = MSoMaxRow If TOnum MSoMaxRow Then TOnum = MSoMaxRow Else If FMnum MSoMaxCol Then FMnum = MSoMaxCol If TOnum MSoMaxCol Then TOnum = MSoMaxCol End If INnumQty = Abs(TOnum - FMnum) + 1 If FMnum <= TOnum Then StepVal = 1 Else StepVal = -1 If bWantOutput Then ScanQty = INnumQty GoSub AllocateOutP End If GoSub A_Ws_Scan 'f Main Loops ElseIf bRowNum Then 'range input, 1 or more areas For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Row TOnum = FMnum + FMnumOrRng.Areas(Aix).Rows.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix Else For Aix = 1 To FMnumOrRng.Areas.Count FMnum = FMnumOrRng.Areas(Aix).Column TOnum = FMnum + FMnumOrRng.Areas(Aix).Columns.Count - 1 StepVal = 1 GoSub A_Ws_Scan Next Aix End If If b1DimOut And HiddenQty 0 Then ReDim Preserve _ OutAyOrRng(1 To HiddenQty) HidnQtyF = HiddenQty 'mainline end Exit Function A_Ws_Scan: 'Scan rows or columns, count, write outputs per function parms. ScanQty = Abs(TOnum - FMnum) + 1 If bRangeIn Then INnumQty = INnumQty + ScanQty If Not bWantOutput Then 'count only. If bRowNum Then For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC Else For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then HiddenQty = HiddenQty + 1 If bExitFirHidn Then Return End If Next RC End If ElseIf bRowNum Then 'Scan, write outputs, hidden rows If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Rows(RC).Hidden Then If Not bExitFirHidn Then 'Scan all for hidden 'til input rows end. If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC 'f updated array Else InnerRC = RC 'f update range when contiguous hidden's end. Do While .Rows(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 RCAdr = RC & ":" & InnerRC GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else RCAdr = RC GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC Else 'Scan, write outputs, hidden columns If bRangeIn Then GoSub AllocateOutP For RC = FMnum To TOnum Step StepVal If .Columns(RC).Hidden Then If Not bExitFirHidn Then If b1DimOut Then HiddenQty = HiddenQty + 1 OutAyOrRng(HiddenQty) = RC Else InnerRC = RC Do While .Columns(InnerRC + StepVal).Hidden And _ Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty InnerRC = InnerRC + StepVal Loop HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1 'RCAdr = ColRngAdrF(RC, InnerRC) '$A:$B from col #'s Col1 = RC Col2 = InnerRC GoSub ComposeColAdr GoSub AddToOutRange RC = InnerRC 'back to For/Next End If 'updated range Else HiddenQty = 1 If b1DimOut Then OutAyOrRng(1) = RC Else 'RCAdr = ColRngAdrF(RC, RC) Col1 = RC Col2 = RC GoSub ComposeColAdr GoSub AddToOutRange End If Return End If 'f updated array or range End If 'row is hidden Next RC End If Return AddToOutRange: 'Set with Union or not If bRowNum Then If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Rows(RCAdr)) Else Set OutAyOrRng = .Rows(RCAdr) End If Else If Not OutAyOrRng Is Nothing Then Set OutAyOrRng = Union(OutAyOrRng, .Columns(RCAdr)) Else Set OutAyOrRng = .Columns(RCAdr) End If End If Return AllocateOutP: 'Dim/ReDim array to hold row or column #'s, Init Rng output. If bRangeIn Then 'init on area 1, then redim array for +1 areas If Aix = 1 Then GoSub AllocateAy1st |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening a group but keep hidden rows hidden | Excel Discussion (Misc queries) | |||
Rows hidden by Autofilter vs hidden by changing the Hidden property | Excel Programming | |||
Formula or Code to keep Hidden Rows Hidden | Excel Worksheet Functions | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
Testing for hidden rows | Excel Programming |