How to use Wrap Text in Excel VBA

In this tutorial, you will learn about how to Use Wrap Text in Excel VBA.

What is Wrap Text?

If we have a single cell text length, reach up to multiple cells or columns and we want to adjust this text into a single cell.

Suppose that we have a text data in cell “A1” (See image below), and this text length reaches up to the next 3 cells or the next 3 columns. 

See image below:-

But we want to adjust this whole text data into a single cell.

Now with the help of Visual Basic, I will adjust the whole text in a single cell by using the “Wrap Text” method in VBA.

Go to the Developer tab and Click Visual Basic

Insert a new module and write the below-given code.

See the first image where our cell data exist in the original format.

In the above image, I have written two lines of code.

This is a simple code of converting our text data as “Wrap Text” and again convert this text as a normal mode by executing another line of code.

Now I will execute the code by pressing ‘F8’ function key for step by step execution.

By executing the first line as “True”, convert our text data as Wrap Text or adjust our all text data into one cell.

Again pressing “F8” for executing our second line of code, this convert our “Wrap Text” data into normal text data or in the usual format.

I hope this small tutorial help to understand this method of VBA to Wrap Text in Excel.

I hope you find this helpful.

Please feel free to put your comments to give me a small boost.

Regards

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Clear Cell Formatting in Excel VBA

Text Alignment in Excel VBA

Excel VBA - Orientation (In Hindi)

Text Alignment in Excel VBA

In this tutorial, you will learn about Text Alignment in Excel VBA.  First, we must know that what is the text alignment in Excel and then we will apply this with the help of Excel VBA macros.


So alignment the text in Excel, align text dataset in a cell, “Left, Center, Right” or “Top, Middle, Bottom”. We align the text in Excel as per our dataset requirement.


For aligning option, go to the “Home” tab and search for “Alignment” group, see the image below.

There are two types of text alignment in Excel. Understand this with the help of the below example.

1. Vertical Alignment (Top, Center, Bottom)

2. Horizontal alignment (Left, Center, Right)

Now we will understand these alignment step by step.

     Vertical Alignment (Top, Center, Bottom)

In this alignment, our text data move vertically only, such as bottom to top or top to bottom, three-steps: - Top, Middle, and Bottom.

See image below for Vertical Alignment:-

Horizontal alignment (Left, Center, Right)

Horizontal Alignment: - In this alignment, our text moves “horizontally”, i.e.  From left to right or right to left, with three-steps: - Left, Center, or Right.

See image below for Horizontal Alignment:-

Now with the help of VBA macros I will align the below-given dataset range from “A1: C3”.

See image below:-

Now with the help of VBA macros, I will align the text both ways: - First vertically and horizontally.

1.  Vertical Alignment=Top.

All text has been shifted to top of the cell, see below:-

2. Vertical Alignment=Center

All the text has been shifted to the center of the cell.

3. Vertical Alignment=Bottom

All the text has been shifted to the bottom of the cell

4. Horizontal Alignment = Left

All the text value has been shifted to the extreme left.

5. Horizontal Alignment = Center

See in this image, all text values shifted to the middle of the cell.

6. Horizontal Alignment = Right

See here, the text value shifted to the extreme right of the cell.

So here we learn about 6 types of text alignment in Excel VBA.

I hope you find this article helpful.

Please feel free to put your comments in the below box.

Regards

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Apply Border Style and Thickness in Excel VBA

How to Clear Cell Formatting in Excel VBA

Excel VBA - Alignment of Text (In Hindi)

How to Clear Cell Formatting in Excel VBA

In this blog post, you will learn about how to remove multiple formatting from our dataset.

Sometimes we face the situation when we have a dataset with unwanted formatting.

In the below dataset, we applied some formatting in-text range (“A1: A10”), such as Bold Font, Comments, Hyperlink, Bold, Italic, and Underline with a color background.

Now with the help of VBA, we will remove these formatting one by one.

So for that:-

Go to Developer Tab and click on Visual Basic and click on Insert and open a new Module and start writing the code.

In the below image I have written some format removal codes.

I will run these lines codes one by one and the formatting will start removing or clear as per the code.

 1.     Clear comments

See in the below image I have executed the “Clear Comments” code and comments have been removed from the dataset.

2.  Clear Hyperlinks

As you can see that our next line color has been turned yellow which means, this line of code will be executed now.

This is the sign, which line is being executed.

See in the dataset, we have “Hyperlinks” in range (“A5: A7”), light blue color text.

Our next code with remove hyperlink from the dataset.

See “Hyperlinks” has been removed but the formatting is still existing.

3.  Clear Formats

There are multiple formats in this dataset, executing this line of code will remove all the formattings. But our dataset will remain there without any formatting.

4. Clear Contents

Clear Contents will remove all the existing contents within the range. If the dataset contains any formatting, it will remain the same, i.e. this line of code will nothing to do with it.

To test this line of code, I add some formatting in this dataset, like background-color, bold font, Italic and underline, etc.

Now I will execute the code for “Clear Contents” and see what happens.

See in the above image, the contents with the range (“A1: A10”) has been removed but all the formatting still remain including the comments.

The font was Bold, Italic and Underline in this range, now if we again write anything within this range, the text will automatically change to Bold, Italic, or underline because formatting still existing in this range.

Just try this.

5.   Clear

The last line of code is “Clear”.

Running this line of code “Clear” means, removing everything.

Now again I have applied multiple formatting within the dataset to test this line of code.

See image below:-

Now I will execute the last line of code “Clear” and see that everything like- formatting and contents including comments or background color will be removed quickly.

See that everything has been removed within the range.

So I hope you find this tutorial useful.

Please feel free to put your comments to give me a small boost.

Regards

3 Easiest Way to Copy and Paste Data in Excel VBA

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

How to Apply Border Style and Thickness in Excel VBA

How to Apply Border Style and Thicknes in Excel VBA

In this blog post, you will learn about how to apply a border and its style in Excel VBA.

Suppose that we have a dataset from range “A1: B6”, and we want to apply borders in this area by Excel VBA.

First of all, we must know what is a border and style in Excel and how to apply it in the given dataset.

Select the dataset first, where we want to apply the border style and then go to Excel “Home” tab, in the “Font” group.

Click on “Border” icon, see image below:-

Click on the drop-down arrow and there are multiples of border options, which we can apply in our dataset.

Now we will do all this with the help of Excel VBA.

See the below image:-

In the above image, we have a range “A1: B6”, where to apply the border.

Our first code is:-

1.  ‘Range ("A1:B6").Borders.LineStyle = xlDot’

Applying this code, dotted lines appear on the borders of our dataset.

2.    Range("A1:B6").Borders.Color = vbBlue

By writing this line of code, the color of our border changed as ‘Blue’.

In this method, we can apply the "8 standard colors" of our borderlines.

See the 8 standard colors in the previous VBA tutorial.

3.  Range("A1:B6").Borders.Value = 4

In this code I have applied a number (4) for the border style. There is some number which is assigned for some particular border style.

4.     Range("A1:B6").Borders.Weight = 3

In this code, we applied “Border. Weight” as 3, border weight is the width or thickness of the border. And we can apply border weight maximum up to “4”.

5.     Range("A1:B6").Borders.LineStyle = xlContinuous

This code represents the border style as continuous, i. e continuous line without any dot or any other style.

6.      Range("A1:B6").Borders.LineStyle = xlDouble

This code represents the double lines as border style.

7.      Range("A1:B6").Borders.LineStyle = xlNone

And in the bottom, if you remove all border-style then write this code as “xlNone”.

Here in this blog, I have written and described some basic code to apply the border styles in our dataset.

I hope you find this useful.

Regards

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

3 Easiest Way to Copy and Paste Data in Excel VBA