Resetting last cell range

[MikeM]

I've tried it all.... No matter what I do, I can not reset the last used
cell range. It goes to cell IV65536. Can I force a reset so that the range
would only be A1:W116?

I ran a few Macros available online, as well as Deleted all rowa except the
ones I wanted. I closed and reopened, I reset any and all mergerd cell... I
am stumped!
Hlpe

 

[Susan]

try copying a1:w116 to a new, unused workbook.
then delete the old workbook (assuming it doesn't contain macros or
things that would need to be moved over to the new workbook).
hope that helps!

susan

 

[Susan]

another solution would be to abandon the last used cell range
idea............

dim MyLastRow as long
dim MyRange as range

'checks column 1 (A) for the last non-blank cell
MyLastRow = worksheets("Sheet1").cells(10000,1).end(xlup).row
set MyRange = worksheets("Sheet1").range("a1:w" & MyLastRow)


just another idea.

susan

 

[Charlie]

Did you delete the rows by highlighting them and pressing the Delete key? Or
did you right-click in the highlighted area and click Delete...

highlight row 117, hold the shift key, press the End key, press the
downarrow key, right-click in the highlighted area and click Delete...

highlight column X, hold the shift key, press the End key, press the
rightarrow key, right-click in the highlighted area and click Delete...

close and save the workbook. Reopen.

One other thing, is the workbook shared?

 

[Joergen Bondesen]

Hi MJM

Try below.

Sub FindLast()
ActiveSheet.UsedRange
Dim x as long
x = ActiveSheet.UsedRange.Rows.Count
ActiveCell.SpecialCells(xlLastCell).Select
End Sub

 

[Ron de Bruin]

See Debra's site
http://www.contextures.com/xlfaqApp.html#Unused

 

[MikeM]

Good afternoon
Joergen Bondesen

The macro you wrote will take me to cell IV65536. The last cell with any
data in it is W116 . I can not clear the USED RANGE area so that it no
longer goes to IV65536.

I could do as susan suggest and put all the data into a new sheet, only
copying the cell's with data and formula's, but there are Macro's that would
also need to be moved that I was hoping not to have to do.

 

[MikeM]

I tried with the info from Debra's site as well as a Macro from
David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Davids has worked very well with other sheets, but not his one?

 

[Ron de Bruin]

hi Mike

If you want send me the file private and i take a look

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I tried with the info from Debra's site as well as a Macro from
David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Davids has worked very well with other sheets, but not his one?

 

[Joergen Bondesen]

Hi MJM



Sorry about my late answer, but I have been on a short holiday.



I forgot one thing in the macro, you must save the file before any change
take place.





Option Explicit



Sub FindLast2()
ActiveSheet.UsedRange
Dim x As Long
x = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.Save
ActiveCell.SpecialCells(xlLastCell).Select
End Sub



I have sometime experienced to have an odd sheet, where above macro can do
nothing.

You can fine such a sheet by selecting a cell in row 1 (and there must be an
empty cell before last row with data) and then hit the keys: End - Arrow
down.

If you end in last row (and there is empty cells in your test column) you
have an odd sheet. I do not know what to do.


I have tried to copy range, but it do not change anything.



If you still reach cell IV65536, you are welcome to send me the file