Solved

excel files that have far too many active cells.

Posted on 2015-01-29
47
265 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
ID: 40578209
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
ID: 40578212
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
ID: 40578268
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
ID: 40578459
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
ID: 40578631
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
ID: 40579293
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 32

Assisted Solution

by:Rob Henson
Rob Henson earned 62 total points
ID: 40579337
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:Danny Child
ID: 40580412
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
ID: 40584934
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 46

Expert Comment

by:Martin Liss
ID: 40628979
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
ID: 40628980
What do I do to get an answer?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40629041
Did you try the code?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40629113
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
ID: 40629119
yes, to other sheets
ok
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40629816
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
ID: 40629864
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
ID: 40629906
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
ID: 40642156
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
ID: 40642698
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
ID: 40654091
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
ID: 40654249
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
ID: 40657653
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
ID: 40657661
in most cases, xls files will "upgrade" to xlsx (2007 format used in 2010 and 2013) with no issues.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Mark88
ID: 40657837
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
ID: 40657907
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
ID: 40658622
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
ID: 40658983
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
ID: 40665522
OK thank you.  I'm going to try that.  You worded that very well
0
 
LVL 23

Expert Comment

by:Brian B
ID: 40667964
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
ID: 40667984
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
ID: 40674465
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
ID: 40674539
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
ID: 40674560
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
ID: 40674570
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
ID: 40674594
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
ID: 40690303
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
ID: 40692010
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
ID: 40692020
There's some type of data fix they are working on too, but Im not aware of the details.
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:Mark88
ID: 40692406
ok checking
0
 

Author Comment

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

Expert Comment

by:Brian B
ID: 40701615
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
ID: 40708367
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
ID: 40710319
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
ID: 40710573
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 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

24 Experts available now in Live!

Get 1:1 Help Now