Excel END HOME problem

[LonnieP]

I've spent the last two days with an END HOME problem - it alway goes to EA358. The column EA1 contains the text 'lastcell', but there's nothing below row 8. I've even moved 'lastcell' to BA1 and cleared the contents, then deleted all the rows between 8 and 500. END Home still goes to EA358. I can't find any page breaks in that area either. If I enter something into a cell below line 358, END HOME moves down there. If I clear the cell, END HOME still stays there. Could default cell formatting cause END HOME to fail? A new sheet seems to work okay. Any Ideas?

I've been trying to use END HOME to find the bottom of the used area of the sheet so I can make the print range A8:R(bottom row, wherever that may be). The END HOME problem is messing that up. Any thought, or suggestions are welcome. Can't tear my hair out - don't have any.

 

[IRstuff]

IF a new sheet works, why not copy only the stuff you want into the new sheet?

Have you saved, closed, and re-opened the sheet? That usually clears its "memory" of lingering things.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IDS]

I don't use end-home in macros. The problems you are having are probably why. It seems Excel remembers the furthest cell used until you save and re-open.

For your purposes have a look at Activesheet.UsedRange.Select, or variants of that.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LonnieP]

Yes, done all those, except moving all to another 'good' sheet.

Sounds like I shouldn't/can't use END HOME! Anyone have a method/macro that will get to the occupied bottom of a sheet?

LonnieP

 

[IDS]

Anyone have a method/macro that will get to the occupied bottom of a sheet?

I hope you looked two lines up!

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

Like Doug, I have had occasional problems along the lines you describe.[ ] Even an empty cell will be seen as a candidate for END HOME if it has merely been formatted.[ ] The way I have found to be the most bullet-proof is to use "delete entire row" and "delete entire column" to obliterate the stuff between the cell I know is the bottom right hand corner of my used range and the cell that Excel thinks is.[ ] Then save the file, close it, and re-open it.[ ] PIA?[ ] You betcha!

 

[LonnieP]

Doug, Looks like 'usedrange' has promise.

Is there some way to lock the upper left corner on cell A8? I can use 'resize(0,-y)' to 'move' the right edge, it just doesn't work on the top of left edges.

LonnieP

 

[LonnieP]

Denial, Jackpot! Deleting entire rows seems to be doing the trick. Looks like I've got some kind of global format going because when I deleted 100 rows the END HOME went up 100 rows when I saved and re-opened the sheet. I moved everything to another sheet anyway.

Thanks for the tip.

LonnieP

 

[IDS]

Lonnie - The code below will reset a range named "PrintRange" to extend to the bottom-right corner of your used range. When you delete data it will update automatically, without deleting rows or saving. You just have to use clear-all, to get rid of any formats etc.

Sub SetPRange()
Dim NumCells As Variant, PRange As Range, StartRow As Long, StartCol As Long

' Create a range named "PrintRange" with the top left cell in the correct position

Set PRange = Range("PrintRange")

StartRow = PRange.Row
StartCol = PRange.Column

NumCells = URangeSize()
 PRange.Resize(NumCells(1, 1) - StartRow + 1, NumCells(1, 2) - StartCol + 1).Name = "PrintRange"
Set PRange = Nothing

End Sub

Function URangeSize()
Dim URange As Range, NumCells(1 To 1, 1 To 2) As Long
Set URange = ActiveSheet.UsedRange
NumCells(1, 1) = URange.Row + URange.Rows.Count - 1
NumCells(1, 2) = URange.Column + URange.Columns.Count - 1
URangeSize = NumCells
Set URange = Nothing
End Function

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LonnieP]

Doug, your code works perfectly. Even massaged it to trim the PrintRange to column 'R'.

One question - how the heck did you set the upper left corner at 'A8'? Even when I run it from column 'B' it still returns the range starting at 'A8'. Seems like there should be something that sets Row = 8, Column = 1, or something like that. Did you hide a line of code?

LonnieP

 

[IDS]

One question - how the heck did you set the upper left corner at 'A8'? Even when I run it from column 'B' it still returns the range starting at 'A8'. Seems like there should be something that sets Row = 8, Column = 1, or something like that. Did you hide a line of code?

No hidden code. The Resize method changes the size of range, keeping the top-left corner in the same place, so the start of the print range is wherever you made it when you create the "Printrange" range name. To change it, just manually edit the range assigned to "printrange", and the macro will use the new starting point, and adjust the end point to the end of the used range.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LonnieP]

Doug, Yeah, I found the answer when I was documenting the code. I think I got it right.

Sub SetPRange()
' Author - Doug Jenkins, Interactive Design Services
' Create a range named "PrintRange" with the top left cell in the correct position


Dim NumCells As Variant, PRange As Range, StartRow As Long, StartCol As Long
' Declares variables

Set PRange = Range("PrintRange")
' 'Sets' vairable PRange to equal previously established ("PrintRange")
' Must manually create named range of PrintRange with a location of $A$8 before running subroutine first time.

StartRow = PRange.Row
StartCol = PRange.Column
' Extracts StartRow and StartCol from vairable PRange (above)

NumCells = URangeSize()
' Gets NumCells by calling URangeSize() function (below)

PRange.Resize(NumCells(1, 1) - StartRow + 1, NumCells(1, 2) - StartCol - 112).Name = "PrintRange"
' Resizes PRange and makes 'PrintRange' equal to it

Set PRange = Nothing
' Clears value of variable 'PRange'

End Sub

Function URangeSize()
Dim URange As Range, NumCells(1 To 1, 1 To 2) As Long
Set URange = ActiveSheet.UsedRange
NumCells(1, 1) = URange.Row + URange.Rows.Count - 1
NumCells(1, 2) = URange.Column + URange.Columns.Count - 1
URangeSize = NumCells
Set URange = Nothing
End Function

Let me know if I got something wrong. A public discussion of things like this is very educational.

LonnieP

 

[yakpol]

LonnieP, try F5(goto)/Special/Last cell

Regards

Yakpol