For statement behaving very strangely
Hi All,
The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know what is causing this? I have used this sort of code structure many times without any problems. Sub TTS_HideRowsTest() Dim MyRow As Long Sheets("Budget Input").Activate MyRow = 0 For MyRow = 6 To 250 Debug.Print Cells(MyRow, "AB").Address Next End Sub Regards Michael |
For statement behaving very strangely
Hi, Could you clarify what you mean by "unexpected results"? I can't see anything odd about that loop. michael.beckinsale;731113 Wrote: Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know what is causing this? I have used this sort of code structure many times without any problems. Sub TTS_HideRowsTest() Dim MyRow As Long Sheets("Budget Input").Activate MyRow = 0 For MyRow = 6 To 250 Debug.Print Cells(MyRow, "AB").Address Next End Sub Regards Michael -- aflatoon Regards, A. ------------------------------------------------------------------------ aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=204918 http://www.thecodecage.com/forumz |
For statement behaving very strangely
Hi All,
Sorry if anybody has spent any time on this. The code is fine. However l have learnt that the immediate window only appears to be restricted to 198 rows. So if you Debug.Print i for 1 to 1200 only the last 198 appear in the immediate window (eg 1003 to 1200) It would be great if somebody could confirm that l am correct on this. Regards Michael |
For statement behaving very strangely
It's 200 actually. You will see 199, then you have one blank row left at the bottom for entry. michael.beckinsale;731158 Wrote: Hi All, Sorry if anybody has spent any time on this. The code is fine. However l have learnt that the immediate window only appears to be restricted to 198 rows. So if you Debug.Print i for 1 to 1200 only the last 198 appear in the immediate window (eg 1003 to 1200) It would be great if somebody could confirm that l am correct on this. Regards Michael -- aflatoon Regards, A. ------------------------------------------------------------------------ aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=204918 http://www.thecodecage.com/forumz |
For statement behaving very strangely
Either of these should work.
Sub TTS_HideRowsTest() Dim MyRow As Long, sh As Worksheet Set sh = Sheets("Budget Input With sh MyRow = 0 For MyRow = 6 To 250 If sh.Cells(MyRow, "AB").Value = 0 Then Rows(MyRow).Hidden = True End If Next End Sub Or with dynamic range. Sub TTS_HideRowsTest() Dim MyRow As Long, Dim sh As Worksheet Dim lr As Long Set sh = Sheets("Budget Input") lr = sh.Cells(Rows.Count, "AB").End(xlUp).Row For Each c In sh.Range("AB6:AB" & lr) If c.Value = 0 Then Rows(c.Row).Hidden = True End If Next End Sub "michael.beckinsale" wrote in message ... Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know what is causing this? I have used this sort of code structure many times without any problems. Sub TTS_HideRowsTest() Dim MyRow As Long Sheets("Budget Input").Activate MyRow = 0 For MyRow = 6 To 250 Debug.Print Cells(MyRow, "AB").Address Next End Sub Regards Michael |
For statement behaving very strangely
Hi,
The code structure your using is fine but I can't comment on why it wasn't behaving as expected when trying to hide rows. The reason you 'think' it's misbehaving now is because of a limitation of the immediate window which only hold 199lines so in your debug.print statement it is printing all the cell addresses to the immediate window but as those exceed 199 addresses the earlier ones drop off and when finished you only see the last 199. At run time this all happens far too fast for you to see so try this Sub nnn() For x = 1 To 199 Debug.Print x Next End Sub All the numbers are left in the immediate window. Clear the immediate window; and isn't it an irritation there is no programmatic way to do this, change 199 to 200 and try again and you will note the 1 is missing. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "michael.beckinsale" wrote: Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know what is causing this? I have used this sort of code structure many times without any problems. Sub TTS_HideRowsTest() Dim MyRow As Long Sheets("Budget Input").Activate MyRow = 0 For MyRow = 6 To 250 Debug.Print Cells(MyRow, "AB").Address Next End Sub Regards Michael . |
For statement behaving very strangely
Hi,
Just a point. The actual number of lines in the immediate window is 200 and AFAIK there is no way of changing this but only 199 are displayed and no doubt MSFT had a reason for this. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, The code structure your using is fine but I can't comment on why it wasn't behaving as expected when trying to hide rows. The reason you 'think' it's misbehaving now is because of a limitation of the immediate window which only hold 199lines so in your debug.print statement it is printing all the cell addresses to the immediate window but as those exceed 199 addresses the earlier ones drop off and when finished you only see the last 199. At run time this all happens far too fast for you to see so try this Sub nnn() For x = 1 To 199 Debug.Print x Next End Sub All the numbers are left in the immediate window. Clear the immediate window; and isn't it an irritation there is no programmatic way to do this, change 199 to 200 and try again and you will note the 1 is missing. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "michael.beckinsale" wrote: Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know what is causing this? I have used this sort of code structure many times without any problems. Sub TTS_HideRowsTest() Dim MyRow As Long Sheets("Budget Input").Activate MyRow = 0 For MyRow = 6 To 250 Debug.Print Cells(MyRow, "AB").Address Next End Sub Regards Michael . |
For statement behaving very strangely
It would be great if somebody could confirm that l am correct on this.
I have, see my post -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "michael.beckinsale" wrote: Hi All, Sorry if anybody has spent any time on this. The code is fine. However l have learnt that the immediate window only appears to be restricted to 198 rows. So if you Debug.Print i for 1 to 1200 only the last 198 appear in the immediate window (eg 1003 to 1200) It would be great if somebody could confirm that l am correct on this. Regards Michael . |
All times are GMT +1. The time now is 12:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com