Archive for the ‘Visual Basic for Applications’ Category

Excel Split Rows Where Columns Different

December 9, 2010 Leave a comment

I caught a colleague of mine working through a CSV document inĀ  Excel, he was putting blank rows in to separate data by a column, to make it easy for him to analyse the text, he was trying to achieve the results bellow:


1 Server Name Event ID Description
2 Server1 12 Details
3 Server1 12 Details
4 Server1 1 Details
6 Server2 12 Details
7 Server2 12 Details
9 Server3 12 Details

My colleague was manually putting the extra rows in, as row 5 and row 8. Problem was there was about 18,000 rows, over 200 servers. So, I wrote him a short script to do the sorting.

A few days later, he asked me for help modifying the script to select the new column, and to start on a different row. Instead of doing this every time, I modified the script to do it starting on the highlighted cell. So, in the example above click cell A2, and run the script below.

I hope you find it useful:

   1:  Sub InsertLine()
   2:      Dim i As Long
   3:      PreviousLine = ActiveCell.Row
   4:      CurrentLine  = PreviousLine + 1
   5:      ColumnNumber = ActiveCell.Column
   6:      While CurrentLine < Rows.Count
   7:          If Cells(CurrentLine, ColumnNumber) <> Cells(PreviousLine, ColumnNumber) Then
   8:              Rows(CurrentLine).Insert
   9:              CurrentLine = CurrentLine + 1
  10:          End If
  11:          PreviousLine = CurrentLine
  12:          CurrentLine  = CurrentLine + 1
  13:      Wend
  14:  End Sub