INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.

RE: Excel END HOME problem

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/

RE: Excel END HOME problem

(OP)
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

RE: Excel END HOME problem

Quote:

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/

RE: Excel END HOME problem

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!

RE: Excel END HOME problem

(OP)
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

RE: Excel END HOME problem

(OP)
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

RE: Excel END HOME problem

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.

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 Function 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Excel END HOME problem

(OP)
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

RE: Excel END HOME problem

Quote:

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/

RE: Excel END HOME problem

(OP)
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

RE: Excel END HOME problem

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

Regards

Yakpol

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close