DPlot Forum Index DPlot
http://www.dplot.com
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Question for any VBA gurus

 
Post new topic   Reply to topic    DPlot Forum Index -> Excel
View previous topic :: View next topic  
Author Message
DPlotAdmin
Site Admin


Joined: 24 Jun 2003
Posts: 2290
Location: Vicksburg, Mississippi

PostPosted: Thu Jan 20, 2005 11:35 am    Post subject: Question for any VBA gurus Reply with quote

There's a small problem with the DPlot Add-In that I'd like to address but I have reached the limit of my VBA knowledge. Most of the Add-In functions are designed to allow blank rows in the selection, which are simply ignored. So you can have 1000 rows of data followed by one or more blank rows followed by another 1000 rows of data, for example. This leads to an inefficiency when you make a selection by clicking column headers rather than dragging the cursor or using key combinations like Ctrl+Shift+End: the Add-In will look at all 65,536 rows on the sheet to see if there's any data there. If not for the "allow blank rows" feature I could simply stop at the first blank row. This doesn't cause any problems but is of course much slower than it needs to be. Excel obviously knows where the data stops or Ctrl+Shift+End would not work. So... in VBA what's the magic command to return the last used row?
_________________
David Hyde
support@dplot.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DPlotAdmin
Site Admin


Joined: 24 Jun 2003
Posts: 2290
Location: Vicksburg, Mississippi

PostPosted: Sun Jan 23, 2005 2:01 pm    Post subject: Reply with quote

Thanks to Bernard Liengme for the solution. If anyone else has a need, here it is:

Code:
Dim Sel As Object
Dim LastUsedRow As Long

Set Sel = Selection
LastUsedRow = Sel.Find("*", Sel.Cells(1, 1), , , xlByRows, xlPrevious).row


Need a good Excel reference? Check out Bernard's work: http://www.stfx.ca/people/bliengme/, including "A Guide to Microsoft Excel for Scientists and Engineers" and "A Guide to Microsoft Excel for Business and Management"
_________________
David Hyde
support@dplot.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    DPlot Forum Index -> Excel All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group