Excel Macro that addes spaces in front of data in a column based on the number in another column.

I have one column with a number from 0-10, called Level, and another column called description.  I would like a macro that addes 1 space in the front of the description if the number in that row is 1, if it is 2 then I want it to add two spaces in front of the discription, so it looks indented.


Level               Description
0                      Top Level
1                        Next level Dodad
2                          Next level after that.
3                           Two level 3's.
3                           Two level 3's.
4                             Next Level
1                        Another number one.
2                         Two level 2's
2                         Two level 2's
Who is Participating?
Martin LissConnect With a Mentor Older than dirtCommented:
This assumes the data is in columns A and B.

Sub ShiftEm()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row

Application.ScreenUpdating = False

For lngRow = 2 To lngLastRow
    Cells(lngRow, "B") = Space(Cells(lngRow, "A")) & Cells(lngRow, "B")

Application.ScreenUpdating = True
End Sub

Open in new window

Technically you can do it with formulas by using  Concatenate and Vlookup function.

All you need to do is set up a range for a Vlookup table with numbers in the first columns and corresponding spaces in the second column. Then you can copy/paste a formula such as this in the third column next to your data:

Martin LissOlder than dirtCommented:
For greater emphasis you could do something like

Cells(lngRow, "B") = Space(Cells(lngRow, "A") * 4) & Cells(lngRow, "B")
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Although above suggestions are fine I would suggest a different approach.

I would not use in this specific case a VBA solution as it is writing the result on top of the initial column where as if by any chance the macro is run an other time it will keep on adding spaces to already existing spaces.

I would propose to put in Col C2 this formula and drag it down and would couple this with hiding Col B so that you get the Col A that is the Level and then Col C that is the result.
=REPT(" ",A2)&B2

If and when you need to add data you simply unhide Col B and make your changes and then hide again and the C will show results.

pls see workbook.
SmilesxlAuthor Commented:
Thank you!  I actually did use the "*4", it really gives it more emphasis.  Thanks again.
Too Late I guess :)
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
All Courses

From novice to tech pro — start learning today.