CODEWITHSUNDEEP
vbaexcel
PootyToot
PootyToot

Reputation: 329

VBA Excel For Loop not behaving as expected

So I have a two Subs, one called Show_Hide and one called hideAllRows. Both of these subs are used to collapse a large selection of rows in an excel sheet, I have a for loop which checks the section for cells with content, and tells the code not to collapse these cells. The for loop is embedded inside the hideAllRows sub which is not working. The code doesn't return any errors, it just doesn't work as cells with content are hidden when hideAllRows is executed.

The ShowHide sub is called within the hideAllRows sub when the button is pushed, this allows me to use the hideAllRows sub to reference rows which I want to show or hide, this part works. I am using a For loop to detect if rows have content, and if they do, the sheet shouldn't hide them, rather the sheet should show these rows and hide cells that do not contain content. Any suggestions on what i'm doing wrong here? Code below

 Sub Show_Hide(RowColumn As String, RangeSelected As String, Hide As Boolean)
  Application.Calculation = xlCalculationManual
  If RowColumn = "Column" Then
    Columns(RangeSelected).Select
    Selection.EntireColumn.Hidden = Hide
  Else
    Rows(RangeSelected).Select
    Selection.EntireRow.Hidden = Hide
  End If
  Application.Calculation = xlCalculationAutomatic
 End Sub



Sub hideAllRows()

 Call Show_Hide("Row", "7:57", True)
 Call Show_Hide("Row", "59:68", True)
 Call Show_Hide("Row", "70:169", True)
 Call Show_Hide("Row", "171:180", True)
 Call Show_Hide("Row", "182:281", True)
 Call Show_Hide("Row", "283:332", True)
 Call Show_Hide("Row", "334:383", True)
 Call Show_Hide("Row", "385:434", True)
 Call Show_Hide("Row", "436:485", True)
 Call Show_Hide("Row", "487:496", True)
 Call Show_Hide("Row", "498:507", True)
 Call Show_Hide("Row", "509:518", True)

 Call Show_Hide("Row", "529:578", True)
 Call Show_Hide("Row", "580:589", True)
 Call Show_Hide("Row", "591:701", True)
 Call Show_Hide("Row", "703:802", True)
 Call Show_Hide("Row", "804:853", True)
 Call Show_Hide("Row", "855:904", True)
 Call Show_Hide("Row", "906:955", True)
 Call Show_Hide("Row", "957:1006", True)
 Call Show_Hide("Row", "1008:1017", True)
 Call Show_Hide("Row", "1019:1028", True)
 Call Show_Hide("Row", "1030:1039", True)
 Call Show_Hide("Row", "1041:1090", True)
 Call Show_Hide("Row", "1092:1141", True)
 Call Show_Hide("Row", "1143:1192", True)
 Call Show_Hide("Row", "1194:1244", True)
 Call Show_Hide("Row", "1261:1268", True)



 For i = 7 To 1269
    If i = 7 Then i = 1269
    Stuff = ActiveSheet.Cells(i, 1).Value
    If Stuff <> "" Then
        Rows(i & ":" & i).Select
        Selection.EntireRow.Hidden = False
    End If
 Next i
 Call UpdateButton(ActiveSheet.Shapes("Button13" & ActiveSheet.Name), "Show All Rows", "showAllRows")
 End Sub

Upvotes: 1

Views: 202

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Here , at If i = 7 Then i = 1269'you are forcing the For loop to exit.

You need to recheck that part.

Upvotes: 3

Related Questions