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]

Ok I put the code in and I get this error. Invalid procedure call or argument, I placed it before and after the code and it works, but when I place it inside the code it breaks.

I stated I wanted the result it gives you in the immediate window, I didn't state anything about placing it inside the code.

There is no code for TM1RECALC its just hitting F9 or hitting Calculate on the data when you are in Manual Calc mode.

Why would you have Application.Run "TM1RECALC" if you have no macro called "TM1RECALC" ?

Application.Run calls a macro, in this case one called TM1RECALC.

 

[Fluff]

@MARK858
TM1 is IBM software, so TM1RECALC maybe a call to that software.

 

[Suggettm]

@MARK858
TM1 is IBM software, so TM1RECALC maybe a call to that software.

Yes Fluff,

It calls to the software TM1 and then loads the data. It would apply if my computer was on Auto-Calc, but it slows us down so we choose to use manual calc and then hit F9 or "TM1RECALC"

 

[Fluff]

Can you disable TM1RECALC in some way?

 

[Suggettm]

Can you disable TM1RECALC in some way?

Yeah, I just deleted it from the code and it didn't change anything. The code keeps expanding like 125 cells beyond what I need. I can't figure out why the code wants to go 125 rows past what exists.

 

[Suggettm]

Ok I think I am on to something. I left "Rows (1":362").Select alone and then I adjusted Set Rng = Range ("G4", Range(G"50").End(xlUp)) and when I did this my scroll bar went to the correct size, something has to be wrong with my code because when I change to G100 or something everything adjusts again.

Sub RunPACEMODELPICKUP()

Sheets("Pickup Model").Select

Rows("1:362").Select

Selection.EntireRow.Hidden = False

Set Rng = Range("G4", Range("G50").End(xlUp))

For Each cel In Rng

If Not cel.Value > 0 Then

cel.EntireRow.Hidden = True

End If

Next cel

End Sub

 

[Fluff]

I wasn't talking about removing the call from your macro.
I meant preventing the TM1RECALC from running at all. ie disable it, uninstall it &c

 

[Suggettm]

I know it's not TM1RECALC, it has to be how I'm selecting the range.

 

[MARK858]

I know it's not TM1RECALC

How do you know?

Your code doesn't expand the range when I run it.

Do you have any conditional formatting?

 

[Suggettm]

My conditional formatting only applies up to rows 362. 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? The Column with the 0's and 1's is G and it goes from G1:G362