Cell fomatting

The UK's most regular instructor-led training courses.
Training information: excel+courses · Excel course london · VBA training in London

Resolved · Medium Priority · Version 2003

Cell fomatting

Steven has attended:
Excel VBA course

by - delegate Steven [7 posts] (2008 Jul 17 Thu, 16:35) Reply

Is there a VBA method to clean a large amount of data, leaving e.g. #NA/NA cells empty and keeping proper numbers in a desired format?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 27 Thu	Bloomsbury	3	book now	£240	£245
2003	2008 Nov 28 Fri	Southwark	1	book now	£235	£240
2003	2008 Dec 3 Wed	Bloomsbury	0	FULL
2003	2008 Dec 5 Fri	Bayswater	5	book now	£195	£205
2002/XP	2008 Dec 10 Wed	Tooting	0	FULL
2003	2008 Dec 10 Wed	Bloomsbury	0	FULL
Full Schedule: See all 98 Excel Advanced course dates. Bookings currently available until 24th November 2009.

RE: cell fomatting

by - trainer Stephen gold contributer [369 posts] (2008 Aug 19 Tue, 14:44) Reply

Hi Steven

Thanks for your question

The following code uses the IsError property of the range object to determine whether the cell contains an error. If it does, it changes the value to and empty string ""

If IsError(Range("A1").Value) = True Then Range("a1").Value = "" End If

This code checks cell "a1", but you could place this in a loop and have the range determined as a variable so that it would cycle through a range of cells

Regards

Stephen

· Improve Performance Of Your Excel Macros
· Microsoft Excel Training Advanced Courses - Making Data Validation Work For You
· Microsoft Excel Training - Discover A Hidden Power At Your Fingertips
· Do You Really Need Excel VBA Training?
· How to check for and reduce errors in Microsoft Excel

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

Excel tip:

Return to the active cell after scrolling

When I scroll a long way down the screen from a selected cell, I can return to that cell with the Ctrl+Back Space shortcut. The active cell now appears in roughly the middle of the screen.

Shift+Back Space does something similar. Scroll down from the active cell and Shift+Back Space returns me to it and puts the active cell at the top of the screen; scroll up from the active cell and Shift+Back Space returns me to it and puts the active cell at the bottom of the screen.

Note also, that while Ctrl+Back Space will return me back to a selected range, Shift+Back Space only ever returns me to the active cell, which is normally at the top left-hand corner of any selected range.

View all Excel hints and tips

Rate this page: