For statement behaving very strangely

[michael.beckinsale]

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

 

[michael.beckinsale]

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

 

[JLGWhiz]

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

 

[Mike H]

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.

 

[Mike H]

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]

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.