End(xldown)

The UK's most regular instructor-led training courses.
Training information: excel+course · Excel-courses-london · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

End(xldown)

Ashish has attended:
Excel VBA course

by - delegate Ashish [1 post] (2008 Mar 13 Thu, 16:31) Reply

If I have spaces or blanks in my data list End(xldown) will not work, how can I get this work (find the last row)with blank records at random points in my data?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2009 Jan 9 Fri	Bloomsbury	1	book now	£240	£245
2007	2009 Jan 14 Wed	Bloomsbury	5	book now	£235	£245
2002/XP	2009 Jan 14 Wed	Tooting	0	FULL
2003	2009 Jan 16 Fri	Southwark	0	FULL
2007	2009 Jan 21 Wed	Bloomsbury	3	book now	£240	£245
2003	2009 Jan 21 Wed	Southwark	2	book now	£235	£240
Full Schedule: See all 114 Excel Advanced course dates. Bookings currently available until 31st March 2010.

RE: End(xldown)

by - trainer Stephen gold contributer [394 posts] (2008 Mar 28 Fri, 09:38) Reply

Hi ashish

Thank you for your question

If your table contains random blank records then the simplest approach is to use the current region property of the activecell object

The following code would select the surronding cells of cell b2, when this is a cell in the table

Range("b2").CurrentRegion.Select

Hope this is useful, if you have any further queries please do not hesitate to get back to me

Regards

Stephen

· Training Budgets In A Bad Economy
· Tools From Advanced Excel Courses: Goal Seek and Solver
· Reasons Why VBA for Excel Training Fails
· Identifying Suitable Candidates For VBA For Excel Training
· Microsoft Excel Training: Essential for Today's Employees

Please browse our web site to find out more about
excel+course and other Microsoft training courses.

Excel tip:

Stop Formula Returning A "#DIV/0" Error

If a formula returns a #DIV/0 error message there is a way to avoid such results.

For example the formula =A1/B1 will return a #DIV/0 if B1 is empty or a zero.

If you protect your formulas with the ISERROR function, the formula will then look like this:

=IF(ISERROR(A1/B1),0,A1/B1)

In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of A1/B1.

View all Excel hints and tips

Rate this page: