Excel END HOME problem
Excel END HOME problem
(OP)
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.
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.





RE: Excel END HOME problem
Have you saved, closed, and re-opened the sheet? That usually clears its "memory" of lingering things.
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Excel END HOME problem
For your purposes have a look at Activesheet.UsedRange.Select, or variants of that.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel END HOME problem
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
RE: Excel END HOME problem
I hope you looked two lines up!
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel END HOME problem
RE: Excel END HOME problem
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
RE: Excel END HOME problem
Thanks for the tip.
LonnieP
RE: Excel END HOME problem
CODE -->
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 FunctionDoug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel END HOME problem
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
RE: Excel END HOME problem
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/
RE: Excel END HOME problem
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
RE: Excel END HOME problem
Regards
Yakpol