Solved

excel files that have far too many active cells.

Posted on 2015-01-29
47
245 Views
Last Modified: 2016-02-10
One of my eu's is having issues with spreadsheets.  Any assistance is appreciated...

"So many of the spreadsheets I work with have grown out of control and are causing issues at certain points in time, especially when trying to do certain things like adding rows etc.

I have several excel files that have far too many active cells. This is causing issues when new rows need to be added to the worksheet, and often causes the file to become unresponsive. I am looking for solution to change/select the active cells in the worksheet so that my sheet is contained to only the rows/columns needed. In my current sheet, if I hit ctrl+end I go down to 1,048,574 rows. My real active row count is 850."
0
Comment
Question by:Mark88
  • 22
  • 12
  • 4
  • +6
47 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 250 total points
Comment Utility
One solution is to turn off AutoCalculate in Options. After that, things will go quickly but you won't see calculations until you re-calculate (F9).
Another thought that comes to mind is to write a macro to only calculate when you leave a row.  I haven't tried it but expect it should work.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Another possibility is to migrate everything to a DB (Access?) That will take some effort, but if you continue to have this problem, it will pay off in the long-term.
0
 
LVL 30

Assisted Solution

by:flubbster
flubbster earned 63 total points
Comment Utility
This usually happens when you click on the top of a column and select a particular format option. It applies that formatting to the entire column of cells. What needs to be done is to have the formatting for all the rows after your last real active row cleared. The formatting needs to be cleared, not the data (there is no data).

Go here: http://support.microsoft.com/kb/244435

Go down to method 2 and download the format cleaner add-in. It works for all the recent versions. Follow the instructions for loading the format cleaner add-in for your version of excel on the webpage.
0
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 63 total points
Comment Utility
This macro removes all rows and columns without data.
You could put it in your personal macros, then it can be used in all workbooks.

Option Explicit

Sub RemoveNotUsedRowAndColumns()
    Dim rw As Long, rwMax As Long
    Dim col As Integer, colMax As Integer, NbrSheets As Integer, ShNbr As Integer
    Dim ws As Worksheet, wsNow As Worksheet

    Application.ScreenUpdating = False
    Set wsNow = ActiveSheet
    NbrSheets = ActiveWorkbook.Worksheets.Count
    ShNbr = 0

    For Each ws In Worksheets
        ShNbr = ShNbr + 1
        ws.Select
        rwMax = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
        colMax = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
        
        rw = rwMax
        Do While WorksheetFunction.CountA(ws.Rows(rw)) = 0 And rw > 1
            rw = rw - 1
        Loop
        ws.Range(Cells(rw + 1, 1), Cells(rwMax + 1, 1)).EntireRow.Delete Shift:=xlUp

        col = colMax
        Do While WorksheetFunction.CountA(ws.Columns(col)) = 0 And col > 1
            col = col - 1
        Loop
        ws.Range(Cells(1, col + 1), Cells(1, colMax + 1)).EntireColumn.Delete Shift:=xlToLeft
        Range("A1").Select
    Next ws

    Application.ScreenUpdating = True
    wsNow.Select
    MsgBox "Save file and open again, or save with a new name"
End Sub

Open in new window

0
 

Author Comment

by:Mark88
Comment Utility
ok but I dont even understand what that does/means.
0
 
LVL 5

Assisted Solution

by:Hakan Yılmaz
Hakan Yılmaz earned 62 total points
Comment Utility
This is VBA code like recorded macros, but its hand written.
(VBA stands for "Visual Basic for Applications".)
Macros or VBA codes can automatically do a lot of things you do with hand.

To run this code;
Open your file and press ALT+F11 to open VBA Editor.
Find your sheet name in the list on the left side of Editor, and double click it.
Copy and paste this code to text area.
Return back to your file and press ALT+F8, select "RemoveNotUsedRowAndColumns" and click run.
When code finished running, return back to VBA Editor and delete the code you pasted.

Or you may want to leave this code here within your file, and use it whenever you want later.
If you willing to save your book with codes in it, you have to save your file as macro-enabled workbook.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 62 total points
Comment Utility
If you know that your last row is 850, select 851 and then do Shift + Ctrl + End. This will select all the rows down to the end. Delete the rows, not just clear them but physically delete using the Delete Rows button.

Then put the cursor back to the top of the sheet and Save. Ctrl + End should now only go to row 850.

If you have a similar issue with too many columns you can do the same by going to the column just to the right of the known area and deleting columns.

Thanks
Rob H
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
I'd also stay away from Merged cells if at all possible.  Can often make extending data ranges/inserting rows difficult.
0
 

Author Comment

by:Mark88
Comment Utility
Ok.  Also one mgr seems to think that some of the accountants that constantly are putting in links in cells are hurting the documents, too.  Is this an issue?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 168 points for rspahitz's comment #a40578209
Assisted answer: 166 points for Ejgil Hedegaard's comment #a40578459
Assisted answer: 166 points for Rob Henson's comment #a40579337

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:Mark88
Comment Utility
What do I do to get an answer?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
Did you try the code?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Do you mean an answer to the question about links? What sort of links are you referring to? Links to other sheets, other workbooks, websites?

If outside of the existing active area, a new entry will extend the active area to include it but that will not hurt, as you put it, the file.
0
 

Author Comment

by:Mark88
Comment Utility
yes, to other sheets
ok
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
At this point, I'm not sure if you've gotten any of this solved, since there seem to be multiple issues.

>In my current sheet, if I hit ctrl+end I go down to 1,048,574 rows. My real active row count is 850."
Ctrl+End takes you to the potential last row, not the actual last row.  If you want to see what Excel thinks is the real last row, use Ctrl+G (Go to), select the [Special...] button, select option "Last cell" (right middle) and click [OK].

If it truly thinks it's below row 850, you can fix this, by going to what you think is the last row, down one, first column, then Ctrl+Shift+DownArrow+RightArrow to select all those rows.
then on the numbers on the left edge of the sheet, right-click and select Delete.
Save and close the workbook and re-open and Excel should now think that 850 is your last row.
0
 

Author Comment

by:Mark88
Comment Utility
The eu's are getting all sorts of errors.  Sometimes they see "file locked by another user", sometimes the sheet turns white or black and everything disappears, incl the cells.  This is a terrible issue we're having and a lot of it has been going on for years.  Nobody's been able to fix it.  I wonder if theyre supposed to be usign Access, or SQL DB's???

This is causing me a lot of trouble with my boss; maybe even to the point of losing my job.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 250 total points
Comment Utility
I've found that sharing Excel files is often a problem...Access would be much better for this, but has a much steeper learning curve for you (the developer of the tool) and will probably require quite a bit of work to get all the pieces in place.  However, if you want to go in that direction, I can give you some pointers on migrating things...the first part would be to set up a bunch of tables to hold all of the Excel data, but that would require knowing what's in your Excel file so the data can be optimized (although you can just push everything first and migrate it later.
0
 

Author Comment

by:Mark88
Comment Utility
rspahitz, are you familiarized with the issues we're experiencing?  Would you or someone else be available for help one on one at times?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Contact me in my profile and maybe we can work something out if it's beyond the scope of experts exchange
0
 

Author Comment

by:Mark88
Comment Utility
We are also having video issues on the server at the console.  It was hung Friday morning.  Looks like we may be getting a new server.  Waiting....
0
 
LVL 23

Expert Comment

by:Brian B
Comment Utility
A new server isn't going to solve some of those problems. Certainly deleting the extra rows and columns as suggested by Rob Henson will fix the one problem. I'll just add in my own experience, that I never noticed a difference after doing the deletes until I saved, closed and opened the file again.
0
 

Author Comment

by:Mark88
Comment Utility
One user states, "Mark, I saved the reformatted file from .xls (1997 - 2003) to .xlsx and the format was saved; I didn’t have to recreate the file."

Does this have any meaning?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
in most cases, xls files will "upgrade" to xlsx (2007 format used in 2010 and 2013) with no issues.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Mark88
Comment Utility
But I think he's saying that he's using the movement from/to to actually fix the formatting disappearing issue.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 250 total points
Comment Utility
Ah...that's possible, since it's a new format.  I've definitely seen some Excel files that had some corruption in them and it seemed that the only way to fix it was to copy all the pieces into a new workbook.  Migrating like that might do the trick more easily.  However, since the workbooks are so complicated, it's possible that some minor inconsistencies will pop up and need to be fixed so it's good to keep the old files around as reference until you feel confident that everything is accurate.
0
 

Author Comment

by:Mark88
Comment Utility
I am the desktop support text here. I don't know much about database administration. I wonder if I should open the ticket to my managers ask them if they can have a database administrator look at these files in my office. What's the right way to phrase that question to my managers so that it sounds correct and professional?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
First, most companies don't consider spreadsheets to be a "database" even though clearly most are, especially yours that's so data-intensive.  That hurdle may present a problem if you ask for a DBA to examine it.
However, there's nothing wrong with Desktop Support asking for resources to help research a topic.

I'd ask something like, "That spreadsheet has some issues that need some extra attention to solve.  Given the number of tasks I have on my plate, who can I get to help me look into it?"
Hopefully they'll realize you care about this issue and the other things you're working on.  The risk is that they may tell you to forget the rest and work on just this until it's resolved.
0
 

Author Comment

by:Mark88
Comment Utility
OK thank you.  I'm going to try that.  You worded that very well
0
 
LVL 23

Expert Comment

by:Brian B
Comment Utility
HI Mark,

I appreciate that this is important to you, however we seem to have gone off track. Several Experts have recommended some steps for you to take to fix the corruption issue. Did you have any success with those? We seem to have gone off track and started talking about links.

As for you level of expertise. I believe I read in your profile you are working at a helpdesk? There should not be a problem with escalating a problem from level 1 (helpdesk) to second level (DBA, system admin, network admin, or even us here at EE). It lets you get on to other questions and not be "spinning your wheels" trying to get a solution you don't understand.
0
 

Author Comment

by:Mark88
Comment Utility
There were several things that seemed to have bandaided this.  One was the eu's suggestion, and another was unchecking the details pane/preview pane.  http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/05/14/the-definitive-locked-file-post.aspx

Somehow though, I doubt this is a real permanent fix for these issues.

I am the desktop support tech here at this location.
0
 

Author Comment

by:Mark88
Comment Utility
Mgr says I need just to drop it b/c we cannot fight this battle.

What's going to happen to the these xls's as they go?
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
Comment Utility
Have you tried to rebuild the workbooks?
You can create a new workbook then open the problem book and right-click each tab and Copy To the new workbook.
Maybe that will clean up the problem if it's something corrupted inside the original.
Side note: if they're going to tie your hands and then try to blame the problems on you (or pin you for not fixing them), it may be time to look for a new position without that level of politics.
0
 

Author Comment

by:Mark88
Comment Utility
That's what theyve been doing; making hundreds of iterations of the xls.  The files server is full of copies of copies of copies.

Maybe it's time.  havent even been here a year.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Just copying the workbook would not fix internal corruption, but copying individual sheets from one workbook to another might.
If not then copying formulas (Ctrl+A to select an entire sheet then Ctrl+C to paste) to a new workbook might also help with corruption.
It's also possible that the workbook has links to some external file (data source) which is no longer valid so it may sit there trying to connect until it times out.  Copy/Paste may resolve that if you can't find it otherwise.
0
 

Author Comment

by:Mark88
Comment Utility
That's an aweful lot of what-ifs
So far copying the entire xls to the dtop and then putting it back up on the server has been what theyve done.
0
 

Author Comment

by:Mark88
Comment Utility
Apparently we're getting a new server too.  Let's wait and see if that makes a difference
0
 
LVL 23

Expert Comment

by:Brian B
Comment Utility
I guess if they aren't interested in fixing it, that is their problem. Just make sure it is documented somewhere like in a ticket or an email to your manager that this problem will not fix itself and there is some action needed on the user's part.

Other than that, I guess the question can be closed.
0
 

Author Comment

by:Mark88
Comment Utility
There's some type of data fix they are working on too, but Im not aware of the details.
0
 

Author Comment

by:Mark88
Comment Utility
I was thinking of waiting to see what the new server does.  Should we wait?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
How long before the server arrives and is configured? If only a few days or do keep this open
0
 

Author Comment

by:Mark88
Comment Utility
ok checking
0
 

Author Comment

by:Mark88
Comment Utility
Server here... Trying to get the rails installed.  Grrr!
0
 
LVL 23

Expert Comment

by:Brian B
Comment Utility
Most rails just snap into place. Just get help putting the server in to the rails. It doesn't always go smoothly and the last thing you want to do is try holding the server with one hand while lining up the rails with the other.
0
 

Author Comment

by:Mark88
Comment Utility
I had to remove some of the studs in the factory rails and use some bolts I found.  That seems to have worked.  NW team is loading Server 2012 R2
0
 

Author Closing Comment

by:Mark88
Comment Utility
Guess we'll see how it goes as the company deals with the data, switches to the new server, and upgrades the PeopleSoft version.

Thank you for all the help everyone.   Since the NW team moved the backup's to night, people have been complaining a lot less too.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Good luck and let us know if something else comes up.  It seems that things are on their way in the right direction.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now