Pocketcrime04's website

Our website

How To Develop A Bullet Record In Excel With VBA
26.07.2017 10:31

If portion of your occupation entails producing written studies from Excel info, the inability to automate bullet points may be a disappointment. Usually, you want to insert a textual content box and then manually develop the bullets.

This write-up clarifies how you can automate your personal bullet-points in Excel with VBA.

Generating A Bullet Point From A Info Established

We are going to use an case in point of a quarterly product sales summary damaged down into products and variety of income. The data may possibly seem like this comma-delimited record.

Item, Revenue

Widget1,one hundred

Widget2,one hundred thirty

Widget3,one hundred fifty

and so forth
We want to switch the knowledge into the adhering to structure:

Widget1: a hundred
Widget2: one hundred thirty
Widget3: one hundred fifty
We are going to change the knowledge by inserting the character for a bullet level before every single worth and include the whole from the adjacent cell. First, we'll choose the range.

excel vba training london rng as selection

set rng=selection("a1").currentRegion.columns(one)

for x=2 to rng.rows.count
For every single cell we'll include the bullet at the start and rewrite the new benefit to the mobile. The worth for a bullet-stage is 149.

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

Sometimes, it can make sense to use a text box to give more flexibility to the design and style of a report. You can manually add a listing to the box by merely highlighting the textual content, right- clicking and selecting the acceptable alternatives for a bullet checklist.

Introducing A Record To A Textual content Box

If you need to automate a list in a text box, related code is used but you need to have to pick the box and include the text in a solitary 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 textual content worth into the text box.

ActiveSheet.Designs.AddTextbox(1,ileft,itop,iwidth,iheight ).Decide on
Selection.Characters.Textual content = strg

The procedure over inserts the exact same bulleted record into the textual content-box which can be modified by placing properties for font type, coloration and size. If your spreadsheets are effectively developed you can even start to automate the remarks and explanations that go with the information.


The potential to insert a bullet list signifies you can generate nicely-designed reports without using secondary purposes like Phrase or Power Position. It indicates automation and VBA can be more effective and include productivity to your work.


Free website powered by Beep.com
The responsible person for the content of this web site is solely
the webmaster of this website, approachable via this form!