Blue Flower

User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

If part of your work requires making created stories from Excel information, the lack of ability to automate bullet details could be a stress. Normally, you need to insert a text box and then manually produce the bullets.

This article clarifies how you can automate your possess bullet-points in Excel with VBA.

Making A Bullet Stage From A Data Established

We'll use an illustration of a quarterly revenue summary broken down into merchandise and number of product sales. The info may well seem like this comma-delimited checklist.

Merchandise, Revenue


Widget2,a hundred thirty

Widget3,a hundred and fifty

We require to change the knowledge into the following format:

Widget1: a hundred
Widget2: 130
Widget3: one hundred fifty
We are going to transform the information by inserting the character for a bullet position prior to every single price and insert the whole from the adjacent mobile. First, we will select the assortment.

excel vba training london rng as variety

set rng=assortment("a1").currentRegion.columns(1)

for x=2 to rng.rows.rely
For each cell we'll include the bullet at the begin and rewrite the new value to the cell. The price for a bullet-stage is 149.

rng.Rows(x) = Chr(149) & " " & rng.Rows(x) & ": " & rng.row(x).offset(,one)
Up coming

Often, it tends to make perception to use a text box to give more versatility to the layout of a report. You can manually insert a list to the box by merely highlighting the text, appropriate- clicking and picking the suitable possibilities for a bullet listing.

Introducing A List To A Text Box

If you need to have to automate a list in a textual content box, comparable code is utilised but you want to choose the box and add the text in a single variable employing the carriage return character "chr(ten)".

for x=2 to rng.rows

myStr=myStr & Chr(149) & " " & rng.Rows(x) & chr(ten)

The code can now insert the string as the text worth into the textual content box.

ActiveSheet.Designs.AddTextbox(1,ileft,itop,iwidth,iheight ).Select
Selection.Figures.Textual content = strg

The procedure over inserts the same bulleted record into the text-box which can be modified by setting homes for font type, colour and dimension. If your spreadsheets are nicely created you can even get started to automate the comments and explanations that go with the information.


The capacity to insert a bullet listing signifies you can generate nicely-created reports with no employing secondary applications like Phrase or Power Point. It implies automation and VBA can be far more productive and include efficiency to your perform.