CODEWITHSUNDEEP
excel
user3362984
user3362984

Reputation: 1

How can I insert empty rows in Excel based on values in other cells?

I have a list in Excel that looks like:

1990 1994 1995 1999

Is there any way to automatically insert a row in between each row for the next year (e.g. three columns after 1990)? Is there any way to change the number of rows inserted?

Thank you.

Upvotes: 0

Views: 121

Answers (1)

TesseractE
TesseractE

Reputation: 425

Give this a try... Worked for me on Excel 2013:

Sub SplitYears()
'
' SplitYears Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Dim NumRows As Integer

    NumRows = ActiveCell.Offset(1).Value - ActiveCell.Value - 1

    If NumRows < 1 Then
        Exit Sub
    End If

    Rows(ActiveCell.Offset(1).Row & ":" & ActiveCell.Offset(NumRows).Row).Select
    Selection.Insert


End Sub

Click on the first year in the list, then trigger the macro. It should insert the appropriate number of empty rows between the Active Cell and the year in the next row down.

Edit: Add SplitAllYears

Starting with your cursor on the first year in the list again, this should go through and space out all of the years in the column.

Sub SplitAllYears()
'
' SplitAllYears Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Dim NumRows As Integer
    Dim StartPoint As String

Beginning:

    NumRows = ActiveCell.Offset(1).Value - ActiveCell.Value - 1
    StartPoint = ActiveCell.Address

    If ActiveCell.Value = "" Then
      Exit Sub
    End If

    If (NumRows < 1) Then
      Range(StartPoint).Offset(1).Select
      GoTo Beginning
    End If

    Rows(ActiveCell.Offset(1).Row & ":" & ActiveCell.Offset(NumRows).Row).Select
    Selection.Insert

    Range(StartPoint).Offset(NumRows + 1).Select

    GoTo Beginning

End Sub

Upvotes: 1

Related Questions