Hiding cells using For loop

[dh1996]

Hi guys,

i thought i was getting the hang of VBA but it continues to trip me up in places!

i have the code below, i thought it would be fairly simple and in my head the code makes sense and should work but there's some VBA quirk that's stopping it. if i change "TestRows" in the following code to a number it works perfectly but i need the value to change based on what "TestRows" equals at any given time. any help is greatly appreciated!

Private Sub HideUnusedRuns_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For TestRows = 81 To 1007 Step 56
    If ActiveSheet.Cells(TestRows + 1, 9).Value = "" Then
    ActiveSheet.Range("TestRows: 1032").EntireRow.Hidden = True
    End If
Next TestRows

Application.ScreenUpdating = True
MsgBox "Setup sheet updated!", vbOKOnly & vbInformation
Application.Calculation = xlCalculationAutomatic
    
End Sub

 

[saboh12617]

Hello, i think you meant to write

ActiveSheet.Range(TestRows & ":1032").EntireRow.Hidden = True

However since you go on a loop until 1007 and the instruction is to hide all rows between the testrow and the row 1032, i do not understand the continuation of the loop. I think this would have the same result:

For TestRows = 81 To 1007 Step 56
    If ActiveSheet.Cells(TestRows + 1, 9).Value = "" Then
        ActiveSheet.Range(TestRows & ":1032").EntireRow.Hidden = True
        Exit For
    End If
Next TestRows

 

[dh1996]

Hi Saboh,

i did try that and a few other variations of typing but none worked. In the case of your suggestion i get the following error -

 

[Fluff]

Or maybe

ActiveSheet.Rows(TestRows).Hidden = True

 

[dh1996]

Saboh - basically the test row is the start of a new section, each section is identical but you may only fill say 4 sections but then I want to hide the unfilled sections, so 1007 is the start row of the last section, 1032 is the last row of the last section. i have tried both you solutions, but they flag up the same error i sent before.

Fluff - that would only hide the one row, i need to hide that row and everything below it down to row 1032

 

[dh1996]

Saboh - basically the test row is the start of a new section, each section is identical but you may only fill say 4 sections but then I want to hide the unfilled sections, so 1007 is the start row of the last section, 1032 is the last row of the last section. i have tried both you solutions, but they flag up the same error i sent before.

Fluff - that would only hide the one row, i need to hide that row and everything below it down to row 1032

Slightly off...1007 used to be the last row, but i've since deleted something so it's actually 977 now, but the issue is still the same

 

[Fluff]

i need to hide that row and everything below it down to row 1032

But that means if row I82 is blank you will hide rows 82 to 1032 regardless of whether the other rows are empty

 

[dh1996]

But that means if row I82 is blank you will hide rows 82 to 1032 regardless of whether the other rows are empty

yes, basically you fill each run in as it's completed, so if you only do 4 runs you'll only complete the form for the first 4 runs, so the next X runs can be hidden as you won't be completing them so they will be blank.

 

[Joe4]

Hi Saboh,

i did try that and a few other variations of typing but none worked. In the case of your suggestion i get the following error -

View attachment 123635

For one thing, get rid of the space in this part ": 1032".
It should be ":1032"

 

[dh1996]

For one thing, get rid of the space in this part ": 1032".
It should be ":1032"

wow.... that fixed the issue....it's working now can't believe it was something so simple