Slow file, Huge Name manager list

[Hrishi]

Hello
I have a excel file of just 2MB, containing only words and number with cell formatting. there are no images in it, no heavy data. still this particular file works so slowly. If i have to insert a row or column, it take 30-45 seconds to insert it. I also see huge list (may be in thousands) in name manager in it. Can you help me identifying the root cause of slow operation. And how to delete this name manager list quickly. it allows only one at a time. I might have to spent days in deleting that list.

 

[Alex Blakenburg]

how to delete this name manager list quickly. it allows only one at a time. I might have to spent days in deleting that list.

That seems odd. What happens when you click on a name then hold the shift key down and click on another name further down the list ?
Does it not select the whole group after which you should be able to press delete ?

Also do you have conditional formatting applied to every line in your data set ?

 

[Hrishi]

The Shift key helped; I was able to select multiple names and delete them. Thanks!

I recently applied conditional formatting to just two columns, but the slowdown issue has been around for a while. I had manually colored those rows and columns to visually separate them.

By the way, deleting the names hasn't improved the time it takes to insert rows and columns. What could be causing this slowdown?

 

[MARK858]

Make a copy of your workbook and run the code below on the copy and see if it makes a difference

Sub LoseThatWeight2()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    For x = 1 To Sheets.Count
    On Error Resume Next
        With Sheets(x)
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(rows.Count, columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(rows.Count, columns.Count)).Delete
        End With
     On Error GoTo 0
    Next x
  
    Application.ScreenUpdating = True
End Sub

Please note that you'll lose all formulas below those returning a value

 

[jolivanes]

You can delete all names like Alex said or a short little macro will do it also.

What formulae, if you have, are in the sheets?

 

[Hrishi]

You can delete all names like Alex said or a short little macro will do it also.

What formulae, if you have, are in the sheets?

iferror, sum, multiplication, all simple formulae. these formulae i normally use in all there excel file. but only this particular sheet has problem. other sheet from same file also works fine. is there any way i can share that excel file so that you can have a look at it and teach me how to fix this issue?

 

[jolivanes]

Alex and Mark are specialists in Excel. They will sort your problems.

 

[Alex Blakenburg]

LOL @jolivanes - Thanks, I think

@Hrishi
How may rows of data are there in the worksheet
Using a copy of the workbook try the following and retest after each one.
• IF you are not using any Names then try deleting all the names using Shift to select and retest
• Try @MARK858's vba code. You will need to hit save on the workbook for it to reset the used range after you run it.
The code deletes rows & columns that Excel is including in the UsedRange but are past the Last Cell with data in it.
• In the bottom left corner (in the status bar) does it say Circular References & does it still say that if you only have that workbook open.
• Remove the conditional formatting from the spreadsheet.
• Do you have any links to external workbooks ?
• Are you using formulas such as Indirect, Offset etc ? (Volatile functions)
• If you go Formulas > Calculation Options > set it to manual, does it make a dramatic difference (remember to set it back to automatic)

 

[jolivanes]

"Credit where credit's due" Alex