Reduce file size by setting 'last row'

smeco

Board Regular
Joined
Jan 23, 2004
Messages
51
My files tend to grow rather huge
I've been told that it's also because Excel considers, say, 1000 to be the last relevant row in my file, even though there's data only up to row 200
This is confirmed by the vertical scroll bar, which, when dragged as low as possible, takes me to row 1000

Is there a way of telling Excel that anything below row 200 should be completely ignored?

I've tried deleting all those rows, but that didn't work

thanks in advance
smeco
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

anvil19

Board Regular
Joined
Nov 18, 2003
Messages
230
Hi smeco

This code will adjust the sheet to reference the last used cell in your spreadsheet. Add this to a button, or a macro ...

Code:
Sub LastCellRef()
Dim lLastRow As Long
Dim lLastColumn As Long
Dim lRealLastRow As Long
Dim lRealLastColumn As Long

If ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) = ActiveSheet.Range("A1") Then
Exit Sub
End If

With Range("A1").SpecialCells(xlCellTypeLastCell)
    lLastRow = .Row
    lLastColumn = .Column
    End With
    
lRealLastRow = Cells.Find("*", Range("a1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("a1"), xlFormulas, , xlByColumns, xlPrevious).Column
If lRealLastRow < lLastRow Then
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete
End If
ActiveSheet.UsedRange 'Resets LastCell

End Sub

Try That

anvil19
:eek:
 

smeco

Board Regular
Joined
Jan 23, 2004
Messages
51
Thank you very much
could you also tell me a quicker way please? I mean, I already know I only want to go up to row 200 - is there a way of setting that as the limit, without having to use any code?

thanks again
smeco
 

anvil19

Board Regular
Joined
Nov 18, 2003
Messages
230
Me again

There is no real way that you could tell Excel to use only rows 1 to 200 without using a Macro inside the WorkSheet_Change event. The WorkSheet_Change event will look at the sheet every time it changes, and if you have some code that looks to see if a user is using a row below 200, will pop up a message to tell him the error of his ways. Something Like so :

Add This to your sheets code window (Right Click the tab and select the View Code Option)...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vara, varb

    vara = ActiveCell.Rows.AddressLocal
    varb = Right(vara, 3)

    If IsNumeric(varb) And varb > 200 Then
        MsgBox "You have tried to use a Row above Row 200. Not Good!!!", vbOKOnly + vbCritical
    End If

End Sub

This way you will only have the use of rows up to 200, but any column.

Try That

anvil19
:eek:
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
See http://www.contextures.com/xlfaqApp.html#Unused
smeco said:
My files tend to grow rather huge
I've been told that it's also because Excel considers, say, 1000 to be the last relevant row in my file, even though there's data only up to row 200
This is confirmed by the vertical scroll bar, which, when dragged as low as possible, takes me to row 1000

Is there a way of telling Excel that anything below row 200 should be completely ignored?

I've tried deleting all those rows, but that didn't work

thanks in advance
smeco
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,520
Members
425,479
Latest member
Neerajcool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top