How does this code work????

[K. Georgiadis]

I have received this code (I believe from Firefytr) and it worked perfectly in the attached workbook. Even though I have learnt other techniques for hide\ing/unhiding rows, I want to be certain I understand how this code works so that I can adapt it to other situations where the number of rows per block is different:


Private Sub OptionButton1_Click()
' Auto populate
For i = 5 To Range("A65536").End(xlUp).Row
If Left(Range("A" & i), 4) = "Auto" Then
Rows(i & ":" & i).EntireRow.Hidden = False
Rows(i + 1 & ":" & i + 1).EntireRow.Hidden = True
Range("G4") = True
End If
Next
End Sub


Why is i=5?

In the line:

If Left(Range("A" & i), 4) = "Auto" Then

what is the purpose of 4 inside the brackets and where is "Auto" being read?

All very fundamental, but I am easily confused! :(

[JonPeltier]

I have received this code (I believe from Firefytr) and it worked perfectly in the attached workbook. Even though I have learnt other techniques for hide\ing/unhiding rows, I want to be certain I understand how this code works so that I can adapt it to other situations where the number of rows per block is different:


Private Sub OptionButton1_Click()
' Auto populate
For i = 5 To Range("A65536").End(xlUp).Row
If Left(Range("A" & i), 4) = "Auto" Then
Rows(i & ":" & i).EntireRow.Hidden = False
Rows(i + 1 & ":" & i + 1).EntireRow.Hidden = True
Range("G4") = True
End If
Next
End Sub


Why is i=5?

This is to leave the top 4 rows alone, and only start looking for rows to hide in row 5.


In the line:

If Left(Range("A" & i), 4) = "Auto" Then

what is the purpose of 4 inside the brackets and where is "Auto" being read?

Left(string, N) returns the leftmost N characters of string.

Rather than Range("A" & i), it would be better to use Range("A" & i).Value (even though .Value is the default property of the Range object), if only for readability.

[Bob Phillips]

You also don't need Rows and EntireRow, they are synonomous


Rows(i & ":" & i).EntireRow.Hidden = False

can be


Rows(i).Hidden = False

[JonPeltier]

With Range("A" & i)
If Left$(.Value, 4) = "Auto" Then
.EntireRow.Hidden = False
.Offset(1).EntireRow.Hidden = True
Range("G4") = True
End If
End With

[K. Georgiadis]

Thanks Jon, XLD, it is crystal clear now. Jon, it is good to see your name again; I have always been a fan of your website.