Insert Indent in Excel 2010 Using VBA

Question

I need to indent a column in a spreadsheet up to 2500 rows and would like to automate the indenting process. The number of 'indents' is in another column. I want to indent the name column the number in the Outline_Level -1. I appreciate any help.

Here is the before:

Outline_Level	Name
1	ECLSS - CLIN 1: DDTE/Certification
2	Start/Completion Dates
3	Advanced Design Development
3	CCtCAP Work
2	Program Management Dashboard
3	ETA Block 0.5
4	ETA Block 0.5 Start

Here is the after:

Outline_Level	Name
1	ECLSS - CLIN 1: DDTE/Certification
2	Start/Completion Dates
3	Advanced Design Development
3	CCtCAP Work
2	Program Management Dashboard
3	ETA Block 0.5
4	ETA Block 0.5 Start

Answer 1

Select all the data (not including the header row) and run the following:

Sub AddIndents()

    Dim i As Long

    Dim theRange As Range

'

' Application.ScreenUpdating = False ' Optional

    Set theRange = Selection

'

    For i = 1 To theRange.Rows.Count

        theRange.IndentLevel = 0

        theRange.Cells(i, 2).InsertIndent theRange.Cells(i, 1)

    Next i

' Application.ScreenUpdating = True ' Optional

'

End Sub

Answer 2

That's what happens when you hand-type something - oops!

Glad it helped.

Eric

Answer 3

Thank you E.J. You saved me a LOT of time.

I did move the line 'theRangeIndetLevel = 0' to above the For Next loop.

Over 8000 rows in nothing flat.

Stretch