How To Freeze A Specific Row In Excel Using VBA

[ad_1]

Whenever you have a large amount of data, it can be helpful to keep a specific row in sight when you scroll down the screen. While you can do this manually it makes sense to use VBA to select the row and “freeze” the pane.

This article will show you how to freeze the top pane depending on the amount of data you have.

A Typical Scenario Where The VBA Freeze Pane Command Is Useful

The example we’ll use is a downloaded file which contains more than 100 entries.

It could be a customer or employee list where you would like to “freeze” the top row as you scroll through the entries.

The data might look like this:


Name
======
Emp1
Emp2
Emp3
.
.
Emp100
Emp101

We’ll assume you’re using VBA to save the data into a new worksheet, rather than using a manual copy and paste.

Once your code has finished downloading the data, you might want to freeze the top row if there are more than 100 entries.

First, you’ll need to work out how many entries there are:


dim rng as range
dim entries as long
set rng=range("a1").currentRegion.columns(1)
entries=rng.rows.count

Once you know how many rows are in the data set you can conditionally set the freeze pane command by selecting the row below the specified cell.

We’re going to freeze the top row, but we will need to “unfreeze” any existing panes first.


activeWindow.freezepanes=false
if entries >100 then

range("a2").activate

activeWindow.freezepanes=true
End If

If you need to specify the row to be frozen you can insert the following command in the code.


myRow=x+1
range("a" & x).activate
activeWindow.freezepanes=true

The same technique could be used to set the freeze command based on other variables or criteria. In the example below, the code freezes the pane below the cell containing “January 2013”.


set rng=range("a1").currentRegion.columns(1)
myCell="January 2013"

For x = 1 To rng.Rows.Count

If rng.Rows(x) = str Then

myCell = rng.Rows(x).Offset(1, 0).Address

Exit For

End If
Next
Range(myCell).Activate
ActiveWindow.freezePanes = True

Or, the code could search for a cell with bold type.


For x = 1 To rng.Rows.Count

If rng.Rows(x).font.bold=true Then

myCell = rng.Rows(x).Offset(1, 0).Address

Exit For

End If
Next
Range(myCell).Activate
ActiveWindow.freezePanes = True

You could activate the code in several ways:

  • Set up a worksheet change event to identify when the number of entries exceeds a certain number
  • Write the code into an existing data import procedure
  • Create a tag such as bold type or a cell value to enable the code to identify the correct place to insert the frozen pane.

Summary

Excel is capable of holding large quantities of data, but it’s a good idea to keep the design of your spreadsheet as user-friendly as possible. By using the freeze panes command you can reduce errors and make life easier for anyone using your Excel file.

[ad_2]

Source by Andy L Gibson

2 Essential Oils For Removing Skin Tags

[ad_1]

There are a lot of methods for removing skin tags at home without paying the big fees to a dermatologist. But some of these methods are safer and more effective than others. In this little article, you will learn about two essential oils that can get you rid of your skin tags: Tea tree oil and Castor oil.

1- Tea tree oil is extracted from a plant called Melaleuca Alternifolia. This plant grows in Australia and it was used for hundreds of years by aboriginal Australians to treat skin infections and a big number of other problems.

To use Tea tree oil for removing skin tags, you just soak a Cotton ball in water and then you put a few drops of tea tree oil on it. After that, you’ll have to rub it against your skin tags.

You’ll have to repeat that three times a day for a couple of days until the skin tags fall off.

2- The other essential oil you can use for removing skin tags is Castor oil. It is extracted from a plant called Ricinus Communis. This essential oil is also known for its healing effect on many skin diseases.

To use Castor oil on skin tags, you’ll have to mix it with baking soda. You just mix them together until they form a paste and then you apply that mixture to your skin tags three times a day until they fall off.

These are two remedies that are safe and that leave no scars. But as you can see, they require a good deal of patience on your part.

[ad_2]

Source by Dave Lecky

Are Colombian Quality Emeralds a Good Investment? – A Brief Overview

[ad_1]

In the wake of the housing market melt down and soft market scandals investments in commodities are on the upturn. Emeralds as precious gems have always held value in the market and Colombian quality emeralds can be a harvest asset for any serious investor.

Gems are, ounce for ounce, the most concentrated form wealth man has ever known. Emeralds are extraordinarily profitable, and like other gems easy to store and conceal. An emerald worth millions of dollars could be less than 4 inches long and easily fit in your hand. The price ceiling for high quality emeralds has recently doubled. It jumped from a standard $40,000 per carat for the highest quality emeralds to almost $90,000 per carat for a 9 carat cut Muzu International emerald, which recently sold at Christie’s Auction House in New York. Making it understandable how a one ounce fine Colombian emerald crystal could fetch millions at the current price levels.

Because of the rarity of large cut quality emeralds future prices are only expected to go up. Many investors are buying high quality emeralds to increase and maintain the value of their portfolios. While not everyone can afford investing at those levels, there are alternatives that can prove quite lucrative. As with any investment due diligence is advised before making a commitment. With gemstones, there is a confidentiality of ownership. At present gemstone and diamond transactions are not individually reported to governmental agencies. This however is subject to change, therefore it is recommend one consult a professional tax adviser for your personal reporting requirements.

Below is a brief overview to aid one in making an informed decision when considering an investment in emeralds.

Colombian emeralds, especially those, from the Muzo mines by far command the highest value in the market. Generally speaking they are known for exquisite appearance, large crystal size and few impurities. The Itoco emerald is a perfect example of this. Most emerald dealers agree overall Colombian emeralds are the finest. A high quality Colombian emerald will fetch a 10 to 20% premium over African and Brazilian of similar size and clarity.

Although high end Zambian emeralds do not command the prices of high quality Colombian emeralds, they do have the edge over Brazilian emeralds in today’s market. Zambian emeralds mostly tend to be a medium to light green with a bluish tint. Because of their bright lively color and extraordinary clarity Zambian emeralds were originally suspect to be synthetic by established dealers when first discovered in 1976. Tiffany’s was first to acknowledge Zambian emeralds as authentic in 1989. Since then their popularity has increased and jewelers and dealers alike now recognize them as natural emeralds. Zambia was the first emerald producer to provide an affordable alternative in quality emeralds. For those who can not afford the top grade Colombian emeralds, the non enhanced Zambian emerald may be a good runner up. Emeralds from Zambia are trending now for several reasons.

  • Like the cutters and polishers of Colombian emeralds, the Zambian emerald craftsmen apply the accepted way oiling using only clear oils.
  • Although Zambian emeralds as a rule do not command the prices of high quality Colombian emeralds most are good jewelry quality and some exceptionally large stone bring close to premium prices.

In the early 1960′s vanadium emeralds were discovered in Brazil. Being a form of beryl, but not (chromium) most dealers and gemologists refused to recognize them as emeralds and categorized them as green beryl. After three years of fighting for acceptance in 1963 G.I.A issued a lab report identifying vanadium-colored beryl as natural emeralds. 

Five Tips for Smart Investing

Think Big:

As with most gems large high quality crystals are far more valuable than smaller ones with the same qualities.

Point of Origin:

Large high quality emeralds no matter where they are mined are a good investment, but Colombian emeralds will return a higher resale premium.

Color:

While a deep green is usually considered a good commercial grade, that does not necessarily make it a good investment grade emerald. Brightness or ‘fire’ is an extremely important consideration as well as color when choosing investment grade emeralds.

Inclusions:

The type of inclusions effect an emeralds value. Investment grade emeralds will not have internal fractures, carbon build ups (internal black spots) and will have a good loupe clarity. Translucent to opaque stones, although they often make for beautiful jewelry, should be strictly avoided when investing.

Clarity:

Clean emeralds over three carats often sell for more than comparable sized diamonds. When considering color and clarity its best to choose clean emeralds with a medium color over heavily included emeralds with perfect green color for optimum investment value.

Summary

Over all it is up to the individual in choosing an investment. However, being a rare commodity, high quality emeralds certainly can increase the value of ones portfolio. Although Colombian quality emeralds often are a very good investment it should be noted; this article is merely an overview and does not reflect after the fact market changes nor guarantee a return on ones investment.

[ad_2]

Source by Wesley Ivins

The Importance of Smiley Faces & Emoticons Online

[ad_1]

I’m sure you’ve seen, and probably even used a smiley or emoticon at least once in your internet experience. Whether its been an actual picture, or just a series of characters like:-), you’ve seen it!

You may be surprised to find out how and why these “smileys” have evolved in online communication. Well, it should be noted that psychologists have discovered that 90% of human communication is non-verbal (that’s probably not surprising). A lot of communication is perceived through body language, tone, gestures, etc.

On the internet, whether it’s in a forum, blog, email, or instant messenger chat, there’s no mechanism to convey these “non verbal” signals. Hence, smileys! Smileys and emoticons are used to confer emotion no matter what it is, sarcasm, jokes, anger, sadness, bashfulness, playful, etc.

The first noted emoticon ever noted is “:-J” which represents tongue in cheek.

Today, smiley face symbols and emoticons have become extremely popular in digital communication and have evolved from simple character sequences to simple static images and nowadays 3-dimensional animated smileys. You’ll see support for smileys in all modern email providers (example: Hotmail, Yahoo!, Gmail) and all instant messenger chat applications (example: MSN messenger, AOL messenger, yahoo messenger, Skype).

The popular blog engine, WordPress also has built in support to convert character sequences to images (which can be a pain sometimes for bloggers, but this can be easily disabled).

It’s actually cool to learn how something so seemingly meaningless such as smiley faces can actually have deep psychological implications in communication.

[ad_2]

Source by Stacy Sloan

Are Mopar Parts Are the Best Choice For All Chrysler Vehicles?

[ad_1]

Mopar Parts Are The Best Choice For All Chrysler Vehicles

When it comes to replacement automotive parts, Mopar parts are the most sought after for use in Chrysler vehicles. Mopar, an acronym for motor parts, is the official parts and service division of the Daimler-Chrysler Corporation. Mopar is a name and that is known for precision fit and quality that Chrysler product honors with to with confidence.

The Mopar concept is about being sure that replacement parts will fit your car just like the original equipment. Since genuine Mopar parts are manufactured by the same people who engineered and built the original vehicle, car owners can be sure the parts will perform with reliability.

The Mopar name was first used by Chrysler over eighty years ago and has been considered to be synonymous with quality and dependability in terms of both repair and modification. Over time, the term ‘Mopar” was given broader usage among many car enthusiasts and now it is often used to refer to any Chrysler brand parts, or the automobiles themselves, including Dodge and Plymouth cars. After Chrysler’s purchase of the AMC and Jeep vehicle brands around twenty years ago, the name was applied to them as well.

In addition to replacement repair parts for Chrysler automobiles, the company produces after market modification parts designed for performance on both street and dragstrip machines. Mopar is a term well known in relation to power enhancement and automotive racing equipment.

Original equipment, or OEM parts, means they are guaranteed to fit and perform perfectly. In fact, they are warranted just the same as original equipment on a Chrysler built vehicle. They are considered to be covered by some of the strongest warranties in the all the automotive industry. This is a statement few other parts manufacturers can lay claim to.

Mopar not only provides parts for newer vehicles, but also antique parts and accessories for Chrysler produced cars. Mopar antique parts are precisely designed and engineered to be exactly like the original parts the cars were equipped with. Antique Mopar parts must pass the same equipment tests and standards that were used in the original production of the vehicles.

The company takes care to be sure that only the highest quality materials are used and will fit exactly. The main advantage of Mopar products is that they are considered to be highly superior in comparison to other non-original antique auto parts that are available on the market. The Chrysler vehicle types have a very impressive reputation in the automotive industry among users all around the world. Car part makers such as Mopar have a name to protect and they make sure that all the products they produce will maintain the good reputation they have been able to earn in their many long years of serving Chrysler customers.

Mopar parts also have the reputation for being easy to find. Buyers especially have little difficulty in locating the items they need through the technology of on-line marketing. An amazingly vast array of stores are out on the Internet net for buyers to obtain just about any part or accessory. Without a doubt, Mopar parts are the best choice for replacement in Chrysler automobiles.

[ad_2]

Source by Jim Maxwell