How do I reduce the amount of columns in a sheet?


D

D_N_A

Hi,
I use Excel 2007 Home and Student.
It has a lot of columns (max. number: XFD. you do the math). that means the
horizontal scroll bar is very not sensitive, and not helpful for using only 3
charts. Can I make the sheet offer me less columns?
Thanks a lot,
D_N_A
 
Ad

Advertisements

P

Pete_UK

You can't remove the columns (many people asked for more columns than
the 256 limit in earlier versions !!), but what you can do is hide the
columns that you don't need (or set them to zero width).

Hope this helps.

Pete
 
F

FSt1

hi
a lot of people wanted more rows than 65536 and more columns than 255 so now
we deal with the problems more rows and colmumns presents. Isn't progress
WONderful!?!?
"be careful of what you ask. you might get it."
i'll get off my soapbox now.
have you tried saving the file as an xls instead of xlsx ie going back to
the fewer rows and columns.????
i don't have 07 so i'm doing a lot of wild guessing now.

regards
FSt1
 
R

Roger Govier

Pete

The problem with just hiding the columns, is that it doesn't prevent you
scrolling through the hidden columns, and scrolling seemed to be the problem
the OP was complaining about.

An alternative would be to restrict the scroll area of the sheet. This would
require some VBA code to set the scroll area each time the workbook was
opened.

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:Z100"
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Double Click ThisWorkbook in the Project Explorer pane
Paste code into white pane that appears
Alt+F11 to return to Excel

The code will be triggered automatically as the workbook is opened and will
limit you to an area of the sheet from A1 to Z100.
Amend range to suit.
 
D

D_N_A

D_N_A said:
Hi,
I use Excel 2007 Home and Student.
It has a lot of columns (max. number: XFD. you do the math). that means the
horizontal scroll bar is very not sensitive, and not helpful for using only 3
charts. Can I make the sheet offer me less columns?
Thanks a lot,
D_N_A

I have discovered something weird.
In a new clean sheet, the scrolling bar scrolls from A to M.
But in the sheet I worked on and was talking about, the scrolling is from A
to the last column XFD.
When I deleted all of the graphs from my sheet (Only after there was no
graph in it), the scrolling bar suddenly turned normal.
Can someone solve the problem?
 
D

D_N_A

D_N_A said:
I have discovered something weird.
In a new clean sheet, the scrolling bar scrolls from A to M.
But in the sheet I worked on and was talking about, the scrolling is from A
to the last column XFD.
When I deleted all of the graphs from my sheet (Only after there was no
graph in it), the scrolling bar suddenly turned normal.
Can someone solve the problem?

Plus when I saved the sheets with the graphs as Excel 97-2003 the same
scroling problem occured, only that the scrolling was from A to IV.
Restarting the computer didn't solve the problem.
 
Ad

Advertisements

J

Jon Peltier

The scrolling usually involves only what Excel thinks it the used range.
Excel in this case thinks your used range includes all columns.

Select the first column in your sheet that you consider blank. Click the End
button on the keyboard, then hold Shift and click the right arrow. This
selects all columns from the one you selected to the right edge of the
sheet. Right click on the column header and select Delete to delete these
columns, and whatever about them Excel thinks you are using. After saving
the workbook, Excel should now consider only the undeleted columns as the
used range.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 
B

Bill Sharpe

D_N_A said:
Plus when I saved the sheets with the graphs as Excel 97-2003 the same
scroling problem occured, only that the scrolling was from A to IV.
Restarting the computer didn't solve the problem.

Consider putting the charts on separate worksheets.

Bill
 
T

Trekman

Nice Idea, but didn't work, although I saved and exited the program.

There is something that Excel thinks you have as valid data in columns
you believe blank. Scroll bar should normally be only those columns
you are actively using. Are you formatting rows by selecting the
entire row or are you selecting a finite range of cells to format? I
have seen many spreadsheets with lines or shading etc. that goes all
the way out to the limit. I believe this may be the problem.
 
D

D_N_A

I have selected the names of the columns, so It selected the whole column for
every column from around M untill the end. As I sayd earlier, the problem
appears only when the graphs are in the sheet, and disappears when they all
(and not anything else) are deleted. This is, I believe, the sorce of the
problem..
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top