Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

cell fomatting

ResolvedVersion 2003

Steven has attended:
Excel VBA Intro Intermediate course

Cell fomatting

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?

RE: cell fomatting

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

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Customize the toolbar in Excel 2010

You can create your own toolbar which contains your favourite or most used tools. This will make using Excel much more efficient. To do this, you need to click on View, then select Customize Quick access Toolbars and then select Customize. A list of tools will then appear on the screen of which you can add or remove them as you please.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.