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]

What happens with the code below? and what is in the macro "TM1RECALC"?

Sub RunPACEMODELPICKUP()
    Dim x As Long

    Sheets("Pickup Model").Select

    Application.Run "TM1RECALC"

    x = ActiveSheet.UsedRange.Rows.Count

    Rows("1:362").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
 
    x = ActiveSheet.UsedRange.Rows.Count

End Sub

 

[Suggettm]

Hey MARK858,

I tried the new code and nothing happened. I even then tried the Delete, CTRL+Home, Save trick again and that didn't work either. I'm not sure why my scroll bar is not resizing still. TM1 is a application we use from IBM and TM1RECALC just refreshs the data from the database, it's essentially the same as hitting F9 to refresh the data.

Thanks for responding, my answer was a little delayed however, but I finally got around to testing it.

 

[Fluff]

Have you tried deleting the columns, as well as the rows?
ie select & delete the rows, then select & delete the columns, then save

 

[Suggettm]

Ok. I just tried to Select and then delete all the columns, then rows, then I did CTRL+ Home, Save. And that did not work. The rows/columns continued to expand when I do this. I had a feeling it was something to do with my VBA Code, however if I copy and paste the template into a new worksheet it fixes the rows and columns problem, but then since I have so many data connections within the data the worksheet gets mad at me because it doesn't like what I've done to it lol. (It asks for connections and then won't connect etc.) I'd rather fix the problem if possible vs moving the data over to a new worksheet.

 

[Suggettm]

Ok. So if I delete all the data and shift it over, then delete the columns and rows, thereby moving the data back over to cell A1. It fixes the problem and the scroll bar resizes properly. (I get a ton of reference errors when I do this however) so is there another solution or what is causing the problem?

 

[MARK858]

What does

Sub xxxx()
    Dim x As String
    x = Cells(ActiveSheet.Cells.SpecialCells(11).Row, Cells(ActiveSheet.Cells.SpecialCells(11).Row, Columns.Count).End(xlToLeft).Column).Address
Debug.Print x
Debug.Print Asc(Range(x))
End Sub

give you in the immediate window before you do the delete/shift?

 

[Suggettm]

I'll try that, but I was able to create a duplicate sheet, move the info over, delete out the old tab, and then replace the information on the correct tab. Then I went from 1:1750 rows to 1:362 rows like it should. Then after I thought I fixed everything, I re-ran the macro and the data moved out to 1:550 rows and then when I tried the Delete/Home trick it extended another 50 rows to 1:600 rows. So the problem has to be in the macro.

 

[MARK858]

As asked in post number 2

and what is in the macro "TM1RECALC"?

that means post the code

and still answer post number 7

 

[Suggettm]

As asked in post number 2



that means post the code

and still answer post number 7

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.

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

Should I not have that Next Cel at the end of my code?
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

End Sub