vertical scrollbar

[jfig]

I have DELETED unused rows and columns, CONTROL/END goes to the end of
good data, and I have SAVED and CLOSED the worksheet, but the vertical
scrollbar is still acting like I have 65K rows. There are no comments
in the first row (a problem according to some posts), and I had the
cursor in A1 when I SAVED--another post). I did FORMAT some columns
before I DELETED the bottom 63K rows.
Another oddity is that the little up/down arrows (the kind that show
for a FILTER being on) stay in some columns of row 2 and won't go
away.
Any help appreciated; I've been trying to get the vertical scrollbar
to work correctly for a year.
Joann

 

[jpdphd]

I have DELETED unused rows and columns, CONTROL/END goes to the end of
good data, and I have SAVED and CLOSED the worksheet, but the vertical
scrollbar is still acting like I have 65K rows. There are no comments
in the first row (a problem according to some posts), and I had the
cursor in A1 when I SAVED--another post). I did FORMAT some columns
before I DELETED the bottom 63K rows.
Another oddity is that the little up/down arrows (the kind that show
for a FILTER being on) stay in some columns of row 2 and won't go
away.
Any help appreciated; I've been trying to get the vertical scrollbar
to work correctly for a year.
Joann

Joann,
Maybe if you just select everything you want, copy and paste into a
fresh spreadsheet these problems may go away. But maybe not... Sorry,
it's the best I can do!
jpdphd

 

[CyberTaz]

Hi Joann -

As to issue #1 - I'm afraid you're laboring under a misconception. You
cannot actually delete the rows & columns of an Excel worksheet. No matter
what you do (short of a possibility using VBA) there will always be 256
Columns & 65,536 Rows (Mac versions X & 2004). If you attempt to delete
empty columns or rows outside your occupied workspace they are immediately
replaced with new ones. If you delete columns or rows _within_ your
workspace everything shifts accordingly but the columns/rows are tacked onto
the periphery of the sheet. There are ways to make it _appear_ that they
aren't there, but they are Which method would be most effective depends
on what you are trying to accomplish. If you post back with some detail
about your objective I'm sure some suggestions can be offered.

Since the number of columns & rows remains constant, so do the scroll bars.
However, you can go to Excel> Preferences> View & remove the check that
enables either or both in order to not have them displayed.

For #2 - Just go to Data> Filter> AutoFilter & you should find that
AutoFilter has a check next to it - Select it to turn the feature off.

BTW: It sounds like the vertical scroll bar is operating normally unless
there is more you haven't told us

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[jfig]

Issue #1: I read the suggestion to DELETE unused rows/columns from
someone on this site (Greenblat?). I know I cannot really delete the
rows; the idea was to delete them in case there was something there
that was causing the vertical scrollbar to act like there were 65K
rows. I wanted to be sure they were empty. My other Excel
spreadsheets do NOT act like this. The vertical scrollbar is
proportional to the number of used rows in the worksheet. This is
what I want to make happen. On the 'bad' worksheet, the granularity
of the scrollbar is so fine for the 1000 rows I'm using that it's
nearly useless; the thousandth row is about 1/2 inch from the top on
the scrollbar.

Issue #2: The arrows I spoke of STAY there with the Filter turned
off. I don't mind them in row 1 (headers), but in row 2 they cover
data, and I don't know how they got there because I select the column
before turning on autoFilter.

Thanks for answering, Joann

 

[jfig]

I tried copying the first 60 rows onto a new spreadsheet, and the
scrollbar works fine. Don't know what would happen for the whole
thing. Problem is that I have many complicated formulas on the second
page (worksheet), and I don't know how to move them without messing up
the references. Also, I'd just like to make this thing work
properly!
Your idea does work. Thanks. Joann

 

[CyberTaz]

Hi Joann -

The additional info provides some missing pieces to the puzzle and suggests
that you are dealing with a corrupt sheet. Why your previous efforts haven't
remedied the situation I can't say, but it's obvious something else is
needed. First, though, some further clarification:

How many sheets (containing data) are in the workbook?

Do any of the sheets contain formulas that refer to cells on other sheets?
And is the problem sheet involved in any of those formulas?

Is this the only sheet in the workbook which has a problem? - I take it that
both the funky scroll bar & the list buttons are on that one sheet?

--
Bob Jones
[MVP] Office:Mac

 

[jfig]

Thanks. I have only two worksheets--data on the first sheet and
formulas that refer to that data on the second. The problem sheet is
the data, about 1000 rows. Yes, the bad scrollbar and the list
buttons are on the same sheet. Last night I bit the bullet and copied
the data onto a new workbook, renamed both workbooks so the formula
cell references would be correct, and then copied the formulas and
pasted (special, formulas) into a second shet of the new workbook.
Everything works--no little arrows and a working scrollbar. I'm
willing to go with it, but I'd still like to know if the old one can
be fixed and/or how it got broken.
Joann

 

[CyberTaz]

Excellent work! Exactly what I would have prescribed based on this
additional info, but you've already done it. Glad it worked out.

As far as what caused - you'll probably never know... If the file was
brought from a Windows system it could have very well originated there.

As far as fixing it - don't even try.. In fact;

If you haven't already done so I'd delete the problem sheet from the
workbook altogether - or if I read your message correctly and the working
file is newly created separate from the original, Trash the problem file and
rid your system of it once & for all. I don't know if it's likely to do so,
but there's no point risking the spread of contamination.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

PMFJI,

But you can sometimes get those symptoms when you format an entire column,
or having a name or reference to an entre column. Even though you have
deleted unwanted cells, the reference may still refer to the entire column
letting Excel "think" the sheet is 65K rows deep.