End of File Question

CAW

Board Regular
Joined
Jul 1, 2004
Messages
173
How do you re-establish the end of an Excel 2003 file?

For instance, I have a worksheet that has 250 rows of data, but if I do a Ctrl/End, it takes me down to row 3,400. How do I tell Excel that row 250 is now the end of the file?

I tried setting a print range, but that didn't do it.

Thanks in advance.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Highlight everything under row 250, right click and select "Delete" this will perform the delete operation instead of the clear contents operation. That should clear it up.
 

kmiles

Board Regular
Joined
Apr 1, 2002
Messages
113
Probably the quickest way is to insert a new worksheet, then copy & paste only the cells you need to it, then delete the old worksheet.
 

kmiles

Board Regular
Joined
Apr 1, 2002
Messages
113
Here's a vba script to automate the process for all worksheets in one workbook. I think a picked this up on this board a while ago.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Rows(ws.Range("A65000").End(xlUp).Offset(1, 0).Row & ":65535").EntireRow.Delete
ws.Range(ws.Range("IV1").End(xlToLeft).Offset(0, 1).Address, ws.Range("IV1").Address).EntireColumn.Delete

Next
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

As an fyi this will not delete cells that have a value of "" just empty cells.
 

CAW

Board Regular
Joined
Jul 1, 2004
Messages
173
kmiles said:
Here's a vba script to automate the process for all worksheets in one workbook. I think a picked this up on this board a while ago.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Rows(ws.Range("A65000").End(xlUp).Offset(1, 0).Row & ":65535").EntireRow.Delete
ws.Range(ws.Range("IV1").End(xlToLeft).Offset(0, 1).Address, ws.Range("IV1").Address).EntireColumn.Delete

Next
That looks helpful. Where would it go? Within an auto open sub or something? I'm not very VB savvy. :)
 

CAW

Board Regular
Joined
Jul 1, 2004
Messages
173

ADVERTISEMENT

Oorang said:
As an fyi this will not delete cells that have a value of "" just empty cells.
Yeah, i tried that at first, but it didn't work. I assumed there's something in some cell that's causing all this.

Thanks, though.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Below is a Macro from David McRitchie that deletes all cells to the right and below the selected cell.

Sub makelastcell()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim x As Integer
Dim str As String
Dim xlong As Long, clong As Long, rlong As Long
On Error GoTo 0
x = MsgBox("Do you want the activecell to become " & _
"the lastcell" & Chr(10) & Chr(10) & _
"Press OK to Eliminate all cells beyond " _
& ActiveCell.Address(0, 0) & Chr(10) & _
"Press CANCEL to leave sheet as it is", _
vbOKCancel + vbCritical + vbDefaultButton2)
If x = vbCancel Then Exit Sub
str = ActiveCell.Address
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
xlong = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
'use of filters can interfer with column elimination
Range(Cells(1, ActiveCell.Column + 1), _
Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
Beep
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
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 'Tip73
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

Put in a standard Module.
 

kmiles

Board Regular
Joined
Apr 1, 2002
Messages
113
You can open the VB editor with Alt-F11, right-click on your workbook on the left under projects, click on Insert->Module

When blank screens opens up, paste this in:


Sub reduce_worksheet_size()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Rows(ws.Range("A65000").End(xlUp).Offset(1, 0).Row & ":65535").EntireRow.Delete
ws.Range(ws.Range("IV1").End(xlToLeft).Offset(0, 1).Address, ws.Range("IV1").Address).EntireColumn.Delete

Next

End Sub


Save, then you can run manually by clicking on Tools->Macro and selecting that sub or you can create a form button and assign that macro to that button.
 

CAW

Board Regular
Joined
Jul 1, 2004
Messages
173
kmiles said:
You can open the VB editor with Alt-F11, right-click on your workbook on the left under projects, click on Insert->Module

When blank screens opens up, paste this in:


Sub reduce_worksheet_size()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Rows(ws.Range("A65000").End(xlUp).Offset(1, 0).Row & ":65535").EntireRow.Delete
ws.Range(ws.Range("IV1").End(xlToLeft).Offset(0, 1).Address, ws.Range("IV1").Address).EntireColumn.Delete

Next

End Sub


Save, then you can run manually by clicking on Tools->Macro and selecting that sub or you can create a form button and assign that macro to that button.
Gah! I just tried that and it deleted all my data! Since I'm not saving anything though, no harm no foul. But yeah, that didn't work for me.

I'm going to try Datsmart idea next.

We'll see... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top