Excel Experts, Help me out

[darkxshade]

If it's saved to a network drive, it should be backed up regularly. Go to the folder where the file is saved, right click on the xls, go to properties... is there tab called Previous versions? If so, pick one from a date you knew it worked, click copy button and paste into a personal folder and try that file.

 

[Nebor]

If it's saved to a network drive, it should be backed up regularly. Go to the folder where the file is saved, right click on the xls, go to properties... is there tab called Previous versions? If so, pick one from a date you knew it worked, click copy button and paste into a personal folder and try that file.

Nope, apparently we don't have that enabled.

 

[darkxshade]

I think you may be on to something. It goes to row 1048576. I only have ~220 rows of used space. But when I try to delete the rows between the last used row and row 1048576, it doesn't do anything. They don't go away. Unless I scroll all the way to the top of the spreadsheet, in which case those lower rows become "hidden" and the scroll bar returns to a normal size.

In that case, highlight row 1 and drag downward until you highlighted enough rows to have covered your entire dataset(or highlight row 1, hold ctrl, and click on the row number that would cover the dataset)... copy and paste that into a new worksheet.

 

[spacejamz]

Nope, apparently we don't have that enabled.

Does your company have any Disaster Recovery or Business Continuity Plans?

 

[sdifox]

Does your company have any Disaster Recovery or Business Continuity Plans?

they are running on a spreadsheet... you think? :biggrin:

 

[AreaCode707]

In that case, highlight row 1 and drag downward until you highlighted enough rows to have covered your entire dataset(or highlight row 1, hold ctrl, and click on the row number that would cover the dataset)... copy and paste that into a new worksheet.

This.

Alternative approaches might be to copy a blank cell from the far right of the spreadsheet and paste it over everything unused below your data, just a mass replace.

There is a way to define for Excel what the data set is but I never use the command and it's slipping my mind now.

 

[spacejamz]

they are running on a spreadsheet... you think? :biggrin:

running on a spreadsheet isn't the problem...not backing up data...well, that's another story...

 

[Nebor]

In that case, highlight row 1 and drag downward until you highlighted enough rows to have covered your entire dataset(or highlight row 1, hold ctrl, and click on the row number that would cover the dataset)... copy and paste that into a new worksheet.

This works, but I lose the column & row widths\heights. Still, I could go back and redo them if I have to... but is there another way?

 

[AreaCode707]

This works, but I lose the column & row widths\heights. Still, I could go back and redo them if I have to... but is there another way?

Try cutting and pasting a cell from the far right like I suggested. Cut the cell you want, highlight ALL the blank data below your data set at once, and paste.

I said copy earlier but I don't think copy works, I think you have to cut.

Sometimes that resets the list range.

 

[radhak]

OP, I played around with the file you sent me. Unfortunately I am unable to reproduce your problem. I am able to navigate around the sheet and edit without much problems. It is slightly slow, but then you do have a lot of stuff in that worksheet! My CPU utilization goes to around 25% while opening or closing the file, but otherwise stays very low. Excel takes up around 400MB of memory with just this file open, but that's not unusual for 2007.

I am on Win XP, on an I5 2.53 GHz Thinkpad with 3 GB of RAM.

But your spreadsheet does have some problem.

I checked : your last cell seems to be Row 347, Column 944. (To see for yourself : CTRL-G, ALT-S, ALT-S, enter; or, F5 (Go To), 'Special', 'Last Cell').

But when you click the 'End' then the 'down arrow', the cursor goes all the way to Row 1048576, the last possible row in excel. This tells me that somehow, some content or formatting was done that impacted this last row, and Excel is remembering it despite all efforts to clear it.

The steps to reset the Last Cell (ie, delete stuff you don't want) are given here : http://support.microsoft.com/kb/244435. I tried all that, but did not see any difference : the last cell stays the same, and END+DownArrow still takes me to the ultimate row.

As far as I can see, clearing these rows or deleting them makes no difference - but then, I do not see the show-stopper you see, so whadoIknow? When I try to copy and paste just the relevant data cells into a new file, the file size actually grew from 1.6 MB to 2MB , so even that does not seem to work.

So here's what I recommend : use the link I posted above to clear the Last Cell on your spreadsheet, save, re-open, and see if it makes a difference; maybe I'm not seeing it but you will. If not, identify another machine with larger specs (faster CPU, more memory) and try it there.

Last resort - save it to 2003 version. It might still function.

I'll keep working on this, and tell you if I've a breakthru.

 

[AreaCode707]

Ok, I want a copy of the spreadsheet too. I want to take a stab at it. Will PM you my email.

 

[Binky]

If your formatting is the problem, and that's important, you're screwed.

If this isn't an Excel 2010 file, you could also try converting it to 2010 xlsx format, then convert back to xls format. You might get lucky and find that the conversion stripped/fixed the hidden formatting.

I've seen many excel spreadsheets get screwed up by some mysterious hidden formatting that can't be found. The only fix was to repeatedly try various steps like suggested here (paste special this, that, etc). I think these problems occurred mostly on Excel 2000 or earlier.

 

[Nebor]

I ended up copying the data only, then remaking all the formatting. It took me a couple hours to get everything right, but it's faster than ever now. Unfortunately I'll never know what was causing the problem, but at least some others with some technical background saw some of the same oddities (100,000 rows) as I did...

So problem solved, but the hard way. I hate doing things the hard way, it makes me feel dumb.

 

[radhak]

Well, congrats if it works! You might want to take a backup of that, and regular backups now on.

I am fairly certain the problem was somebody trying to format some cells and ending up formatting the whole column(s) or the whole row(s).

If you are not going to use beyond the first 300 rows or 1000 columns, you might explore protecting the other rows/columns to disable any inadvertant changes to them.

I think you took the smartest way out : whichever eliminates the larger, longer-term headache in the quickest way possible!

I ended up copying the data only, then remaking all the formatting. It took me a couple hours to get everything right, but it's faster than ever now. Unfortunately I'll never know what was causing the problem, but at least some others with some technical background saw some of the same oddities (100,000 rows) as I did...

So problem solved, but the hard way. I hate doing things the hard way, it makes me feel dumb.