Excel worksheet size

  The Bard 19:03 13 Sep 2007
Locked

I have created an Excel spreadsheet but cannot limit its size. In 'Print Preview' it fits nicely onto one A4 page, but when trying to email it spreads to 26 pages! How do I get rid of the thousands of extra rows and columns? I have Office XP.

  VoG II 19:05 13 Sep 2007

Try selecting the area that you want to print then File > Print Area > Set Print Area.

  The Bard 19:16 13 Sep 2007

I can select and print ok, it is when I try to email to a colleague that I cannot get rid of all the other rows and columns.

  VoG II 19:28 13 Sep 2007

If you set the print area then your colleague should receive the file set to print correctly.

Or try ASAP-Utilities click here

On the ASAP menu Sheets > Reset Excel's last cell.

Or click here

Or click here

  The Bard 06:31 14 Sep 2007

I can set and print what I want ok. The problem is when I try to email. All the thousands of unused rows and columns are present, making a file over 30mb! It never arrives as it overloads the server.

  VoG II 23:03 14 Sep 2007

Well, you haven't had any more replies so I'm wondering whether you actually tried any of my suggestions?

What happens if you e-mail the workbook to yourself - does the same thing happen?

Thousands of columns is a bit of an exaggeration unless you are using Excel 2007.

The following code is a surefire way of resetting the used range. Position the cursor in the last cell that you want to be in the used range and run the macro:

Sub makelastcell()
Dim x As Integer
Dim str As String
Dim xlong As Long, clong As Long, rlong As Long
On Error GoTo 0
str = ActiveCell.Address
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
xlong = ActiveSheet.UsedRange.Rows.Count
xlong = ActiveSheet.UsedRange.Columns.Count
Range(Cells(1, ActiveCell.Column + 1), Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
Beep
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
ActiveWorkbook.Save
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
MsgBox "Sorry, Have failed to make " & str & " your last cell"
End Sub

  The Bard 12:41 15 Sep 2007

I have installed ASAP Utilities and correctly set last cell, saved as Excel worksheet, but the file is 3.4mb which is too large to email. I can select and print just the area I want, which is small, but save saves all the thousands of unwanted rows and columns.

  VoG II 12:49 15 Sep 2007

Perhaps you need something like click here which will mail either a specified range or the range of cells that you select.

  The Bard 12:53 15 Sep 2007

I have just checked back on some of my earlier spreadsheets, using the same programme, and they are fine - only the selected area is saved and I have been able to email them back to myself as a spreadsheet(I have 2 email addresses) without any problem. I am very much a novice and I am sure I didn't do anything clever originally.

  Chris the Ancient 15:24 15 Sep 2007

OK, let's try a lateral method.

Open your existing worksheet.

Then... open another new workbook.

Return to the original. Highlight the actual data range, and copy it.

Switch to the new workbook and paste just that data into it. Then try emailing that. If that is a smaller file, you could use that in future because it does sound as though you have done something mighty peculiar to the first one.

  The Bard 18:08 15 Sep 2007

Yes, that has worked! Thanks. Strange though...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?