Scroll Bar Extreme Frustrations!!

[Suggettm]

Ok. So I already know about the highlight all the extra rows then hit Delete, CTRL+Home, and then Save the worksheet and this has worked every time I ever needed it to. However, now when I follow this process I gain like 200 more rows. So I'm like that's weird and I do it again, there by gaining another 200 more rows. So after adding like 1,400 extra rows to my document of 362 I gave up and I have no idea what the problem is. I do have this VBA code, but I don't think it's the culprit. Please help!!

All the code does it open all the rows possible and then hide/unhide the rows that have a 0 or 1.

Sub RunPACEMODELPICKUP()


    Sheets("Pickup Model").Select
    
    Application.Run "TM1RECALC"
    
    Rows("1:362").Select
    
    Selection.EntireRow.Hidden = False
    
    Set Rng = Range("G4", Range("G362").End(xlUp))
     
    For Each cel In Rng
         
        If Not cel.Value > 0 Then
             
            cel.EntireRow.Hidden = True
             
        End If
         
    Next cel
    
     Range("K1").Select
End Sub

 

[MARK858]

When you run your code and the range increases if you go to manage rules in the conditional formatting (with this worksheet clicked) have the rules stayed the same and are there the same number of rules?

I guess the real question is if you were going to hide rows based on the cell being either 0 or 1 what would you do?

To answer your question I would use autofilter rather than loop through the rows.

 

[Suggettm]

When you run your code and the range increases if you go to manage rules in the conditional formatting (with this worksheet clicked) have the rules stayed the same and are there the same number of rules?



To answer your question I would use autofilter rather than loop through the rows.

Ok so to go off that I just did this code instead. Which worked fine, but my rows still expanded to 525 and did not stop at 362.

Sub RunPACEMODELPICKUP()

Sheets("Pickup Model").Select

'Expand all rows on worksheet'

Rows("1:362").Select

Selection.EntireRow.Hidden = False

'Collapse rows based on cell value'

Const COLUMN_NUMBER = "G" 'change as required

Dim Last_Line As Long
Dim lLoop As Long

Last_Line = Range(COLUMN_NUMBER & 65536).End(xlUp).Row

For lLoop = Last_Line To 1 Step -1
If Cells(lLoop, COLUMN_NUMBER) = 0 Then
Cells(lLoop, COLUMN_NUMBER).EntireRow.Hidden = True
End If
Next

End Sub

 

[MARK858]

What happens with

Sub AFit()

    Sheets("Pickup Model").Activate

    If Sheets("Pickup Model").AutoFilterMode Then
        On Error Resume Next
        Sheets("Pickup Model").AutoFilterMode = False
        On Error GoTo 0
    End If
    
    Sheets("Pickup Model").Rows("1:362").Hidden = False
    Range("G3:G362").AutoFilter 1, "<>0", , , 0

End Sub

Btw, not sure why your last code loops to row 1 when it was to G4 originally.

 

[Suggettm]

What happens with

Sub AFit()

    Sheets("Pickup Model").Activate

    If Sheets("Pickup Model").AutoFilterMode Then
        On Error Resume Next
        Sheets("Pickup Model").AutoFilterMode = False
        On Error GoTo 0
    End If
    
    Sheets("Pickup Model").Rows("1:362").Hidden = False
    Range("G3:G362").AutoFilter 1, "<>0", , , 0

End Sub

Btw, not sure why your last code loops to row 1 when it was to G4 originally.

Alright, I tried it and it still expanded to row 525. I'm just gonna leave it as is I guess. There's not much more I can think of. I deleted out Named Ranges, put in all fresh code, copy and pasted into a new sheet, but again once the macro runs each time the worksheet wants to go to row 525.

 

[Suggettm]

Even if I go and hide the rows on the bottom, Excel just adds 125 more rows. It never ends and I can't take it anymore.

 

[MARK858]

Did you check the conditional format rules as requested?

 

[Suggettm]

Ok. So what about this, since there was an auto filter on I was able to clear the filter and when I did my rows opened up, but I re-landed at 362 rows....

 

[Suggettm]

Year I cleared all conditional formatting on the worksheet. Maybe since it is hiding 125 rows of data, it keeps reopening up and adding those 125 rows back. Is there any way to move it back to where it should be.

 

[MARK858]

it keeps reopening up

I don't know what you mean by this

adding those 125 rows back

It can't add them back as they haven't gone anywhere, they are just hidden.

I am afraid I can't help any further as

a) you have software being used that I don't have (and apparently you know isn't the issue)
b) you skipped answering the question about the results of the code I posted.

I will leave this thread now, hopefully you will work out the issue or perhaps someone else will have an idea that I haven't thought of.

 

[Suggettm]

Hey Mark,

Thanks for all your help it was really beneficial and I learned some VBA in the meantime. I'm going to switch my document over to the autofilter as it performs better with it! Thanks!