My spreadsheet only has data in about 50 rows and 10 columns but for some reason the cursor on the right is SO small and is acting like we are using the entire spreadsheet down to row 30K! I remember there was a function that you could highlight the cells you are using and then press control save but that is not working - any suggesions?
[Solved] Changing active cell range in spreadsheet
Not sure but try this macro:Code
Sub Reset_LastCell() ' http://support.microsoft.com/default.aspx?scid=kb;en-us;244435&Product=xlw2K ' Save the lastcell and start there. Set lastcell = Cells.SpecialCells(xlLastCell) ' Set the rowstep and column steps so that it can move toward ' cell A1. rowstep = -1 colstep = -1 ' Loop while it can still move. While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1") ' Test to see if the current column has any data in any ' cells. If Application _ .CountA(Range(Cells(1, lastcell.Column), lastcell)) _ > 0 Then colstep = 0 'If data then stop the stepping ' Test to see if the current row has any data in any cells. ' If data exists, stop row stepping. If Application _ .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _ > 0 Then rowstep = 0 ' Move the lastcell pointer to a new location. Set lastcell = lastcell.Offset(rowstep, colstep) ' Update the status bar with the new "actual" last cell ' location. Application.StatusBar = "Lastcell: " & lastcell.Address Wend ' Clear and delete the "unused" columns. With Range(Cells(1, lastcell.Column + 1), "IV65536") Application.StatusBar = "Deleting column range: " & _ .Address .Clear .Delete End With ' Clear and delete the "unused" rows. With Rows(lastcell.Row + 1 & ":65536") Application.StatusBar = "Deleting Row Range: " & _ .Address .Clear .Delete End With ' Select cell A1. Range("a1").Select ' Reset the status bar to the Microsoft Excel default. Application.StatusBar = False End Sub
Wow - you are a guru! I only have basic understanding as to how to record a macro - how would incorporate your code into a macro?
I'm no guru - the macro was filched from the microsoft knowledge base!
If you mean how do you run the macro then whilst in xl hit Alt+F11. On the left hand side you should see the project explorer window (if not hit Ctrl+R). You should see your workbook listed as VBAProject (yourworkbook name) - right-click on it and choose insert module. Paste the code into this. If you flip back to xl the macros can be run from Tools>Macro>Macros
That what you mean?
Totally works! Awesome thanks
Don’t have an account yet? Register yourself now and be a part of our community!