Excel not responding

[shahdelsol]

I created a workbook 3 years ago and since then each month I have been adding a new tab. I currently have about 36 tabs. All tabs are identical size which they are from A1 to AP69. Not every cell has data but most of cells has something in it so I don't think it is necessarily a big file. Every month I have been copying last tab or one of tabs to create a new tab and add new data. There hasn't been any problem until recently when I am trying to copy new tab and add to the end of tabs I get " Excel not responding" like there is lack of memory or something and goes for ever. One time I didn't cancel and let it go and finally after a few hours it created a new tab. My question is : Is this lack of memory? To me This is not a big file and I have only 36 tabs and tabs/sheets are not big file.

I have also hidden the rest of cells beyond A1 to AP69 and I don't know if this matters or not. My Excel is Home and business 2016 version. ​

Does anyone know why it takes this long?

 

[shahdelsol]

I tried to delete I got memory failed after a few minutes. I tired to clear contents. It took me to my macro but I have this macro all along unless something changed recently in my macro that I am not aware of. Here is the code I use :

[FONT=Verdana]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)[/FONT]
[FONT=Verdana]    Dim changedWorksheet As Worksheet
    Dim findRow As Variant
    Dim commentText As String
    
    Set changedWorksheet = Sh
    
    If changedWorksheet.Name <> "Points" And Target.Cells.Count = 1 Then
        If Not Intersect(Target, Range("O2:AC40")) Is Nothing Then
            If IsEmpty(Target.Value) Then
                'Delete comment
                 ActiveSheet.Unprotect
                Target.ClearComments
                 ActiveSheet.Protect
            Else
                findRow = Application.Match(Target.Value, Worksheets("Points").Columns(9), 0)
                If Not IsError(findRow) Then
                    'Match found
                    commentText = Worksheets("Points").Cells(findRow, "H").Value & " " & "$" & Worksheets("Points").Cells(findRow, "J").Value & " " & Worksheets("Points").Cells(findRow, "L").Value & "P"
                    With Target
                        'Create or change comment
                        ActiveSheet.Unprotect
                        If .Comment Is Nothing Then .AddComment
                        .Comment.Visible = False
                        .Comment.Text Text:=commentText
                        .Comment.Shape.TextFrame.AutoSize = True
                       
                        ActiveSheet.Protect
                    End With
                End If
            End If
        End If
    End If
    
End Sub[/FONT]
[FONT=Verdana]
[/FONT]

You probably have hidden formats or data somewhere on the sheet.
Select all rows below your data & delete them (ie right click & delete) then do the same for the columns. Save the workbook & see where Ctrl End takes you.

 

[Fluff]

Turn the macro into a comment, so that it can't run. Then select the rows & clear. Then select the rows & delete. Do the same with the columns

 

[shahdelsol]

I turned off macro. I was able to clear contents successfully but when I tried to delete. Says not enough memory try to use 64 bit version which I though I already had 64 bit. I ctrl end after clearing contents not deleting I still landed on the last cell of excel.

Turn the macro into a comment, so that it can't run. Then select the rows & clear. Then select the rows & delete. Do the same with the columns

 

[Fluff]

After clearing you still need to physically delete the cells.
If you run this

Sub shahdelsol()
   MsgBox Range("A" & Rows.Count).End(xlUp).Row
End Sub

does it return the last row with data?

 

[shahdelsol]

After clearing contents when I tried to delete, I get error " There is not enough memory to complete this action.... consider using a 64-bit of Microsoft Excel" which I know I have 64 bit excel. I ran your code anyway and the message box says 41. Technically my last cell with data is L69

After clearing you still need to physically delete the cells.
If you run this

Sub shahdelsol()
   MsgBox Range("A" & Rows.Count).End(xlUp).Row
End Sub

does it return the last row with data?

 

[Fluff]

Ok, does this return the correct row

Sub shahdelsol()
   MsgBox Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
End Sub

If not is there a particular column that will always have data on the last row?

 

[shahdelsol]

Yes it did which is 69. L69 has always something in it and it is always my last cell with data. A42 to J69 is always empty. M42 to AP69 also always empty. A1 to AP41 mostly has data.
K42 to L69 always has data.

Ok, does this return the correct row

Sub shahdelsol()
   MsgBox Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
End Sub

If not is there a particular column that will always have data on the last row?

 

[Fluff]

Do all the sheets go to the very cell in the sheet, or are some of them ok?

 

[shahdelsol]

I am sorry that I don't understand your question. Can you please explain?

Do all the sheets go to the very cell in the sheet, or are some of them ok?

 

[Fluff]

If you unhide all the rows and do Ctrl End on every sheet, do you always end up on XFD1048576 or do you end up at the "correct" last cell on some of them?