End of File Question

[CAW]

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.

 

[Oorang]

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]

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]

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]

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

 

[CAW]

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]

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]

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]

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]

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