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.
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
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.
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.
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.