can't delete unused columns in Excel 4

[JohnCHolmes]

I have a worksheet that End-Home insists ends at BM1452, but I want it to end at AJ1452 (It DOES end at AJ1452 @#$%!). So I go to AK1, hit Ctrl-end-home, and Edit/delete/columns.

Well, the problematic columns won't go away. Naturally I saved the file; I even restarted Excel. But those columns still exist for end-home!

My suspicion is that some row or rows has a special attribute, like different row height, or bold, or something. Probably, a whole row(s) was selected, and something applied to it.

But how can I know what it is? How do I beat this beast?

 

[DaleWatson123321]

Hi John,

I expect if you try inerting the following code in your routine, it'll resolve your problem.

Sub Used_CR()
usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
MsgBox usedrows & " Rows " & usedcols & " Columns"
End Sub

You can remove the MsgBox. Just running this seems to "reset" Excel's "brain" regarding what columns and rows are in use.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JohnCHolmes]

Sorry for the response lag. Thanks, Dale, but this didn't score for me. I fear my above "suspicion" is in play, while I don't want to check every attribute in 1452 rows to solve it.

Anything else? Or anyone?

 

[sfvb]

1. Highlight the columns AK:IV
2. Select Edit-->Clear-->All
3. Highlight rows 1453:65536
4. Select Edit-->Clear-->All
5. Save the spreadsheet

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q244435

 

[JohnCHolmes]

Thanks - but still, no dice.

If I can recall it exactly, once in an unrelated sheet I selected a row, and drew a lower border. Thus it went all the way to the last column. It then had the "last cell" problem too. My solution was to select the "afflicted" cells (e.g. AK5 to IV5) and clear the border format, which solved it.

So I'm still stuck, but that was a good shot sfvp.

 

[Loomah]

John
Just another thought....
Can you work around this by using the 'used range' as described in
faq707-2112
???

;-) If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[JohnCHolmes]

Excellent suggestion, Loomah. Star on general principle for promoting a FAQ

Indeed that does find the last cell correctly. It did return 36 as last column, which is AJ (as I want it to).

But I'm still clueless as to what mutation is in play here. ActiveSheet.UsedRange has the "wrong" column also. I
I've poured lighter fluid and acid on those columns and like Richard Simmons they just won't go away.

Thanks still. Your answer should prove useful in another context. And truly outstanding tagline there

 

[rissa]

What you should do is press the Ctrl and the (~) key. This will show you any hidden formulas, lines, etc. in the section that is showing up incorrectly.

 

[MiggyD]

This kind of sounds like a problem I had about a year and a half ago.

For example, I'd fill in cells A1 to D4. Then I'd clear contents of column D, thus leaving A1 to C4 filled in. But, when I used ctrl-end, I'd jump to D4 even after I deleted the D column.

If I'm not mistaken it was Excel 97 (before a SP upgrade to the NT3 I was using.) At that point in time, what I ended up doing was copying the exact cells to another sheet, and deleted the original sheet. And it worked. You may want to try this approach. --MiggyD

Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

 

[JohnCHolmes]

Rissa - nice tip. My sheet from hell laughed at it, but still, nice tip.

Miggy - I guess that's all that's left to do. Maybe this is a misapplication of the line in Risky Business, sometimes you just have to say WTF LOL

 

[xlhelp]

John!

This is getting quite curious and I can't stand the mystery. If the workbook is not highly confidential (and will not jeopardize national security), may I please take a look at it?

Thanks.

Indu

xlwrdhlp@clarica.com

 

[MiggyD]

After re-reading your original post, I was thinking of 'protect workbook' -- I'll be first to admit that I've NEVER used it -- but was wondering if it is a possible cause. --MiggyD

Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

 

[xlhelp]

John!

I have just sent you the fixed up Workbook that you had sent me.

BTW, you have an interesting e-mail address. Lucky that the firewall here didn't reject your e-mail.

The trick to solving your problem is:

Once you highlight columns AK to IV and go to Edit, Delete; what you need to do is save the workbook before doing anything else. Then close Excel. When you open the file again, you will have your desired result.

HTH

Indu

 

[JohnCHolmes]

I've gotta wonder about how some stars are given - there's already one on yours (xlhelp), which didn't come from me.

Not that I'm not grateful. But what you're suggesting is just a subset of sfvb's. Just the same, I used your instructions, verbatim, and no dice. Try it yourself if you preserved the unaltered sheet I sent.

If so, than there would seem to be an installation problem - a site problem - in play for me.

Thanks to all for all your effort.

 

[xlhelp]

Ho, John!

I absolutely agree, the star should be deserved.I hope I can earn that one today.

OK. I had played with your file a little before I arrived at the solution that I sent. The combination that seems to work is this:

Open file. Highlight the whole worksheet by clicking in the grey box between row 1 and column A. Go to Format, Row, Autofit

Highlight column AK through IV by dragging through on the column headings. Go to Edit, Delete.

Save the file.

Now if you Close Excel and Open your file again, it should work.

There is a problem with Microsoft where if the Row height has been ajusted, for some reason it gives a false last cell address. Also, if the last cell reported is wrong, then if you delete the columns (or rows) and keep working with the worksheet, the last cell reverts to it's original wrong state.

I didn't put the two things together. I knew both of them separately, so when one didn't seem to work I tried the second and assumed that was the answer.

I hope that resolves the issue.

Indu

 

[JohnCHolmes]

That's it! It was Row Autofit!

In fact, I did't even have to close Excel or the file - it immediately corrected the last cell as soon as saved. (Though many suggest it, I have never found that closing and reopening accomplished anything, other than I suppose potentially a memory leak correction.) And by the way, I just highlighted BN1 through BN1452. BM1 through BM1452 works too.

Way to go! By now a lot of people have at least looked at or weighed in on this this, and possibly multitudes of others who haven't seen this thread ... have suffered this affliction. But you're the only one who seems to have nailed it down to this obscure bug or "feature!"

Options/Row/Autofit on all rows apparently corrects the last cell misreporting.

Thanks again.

 

[sfvb]

Nice catch xlhelp. That deserves another star.