Home > Applications, Code, Microsoft Excel, Visual Basic for Applications > Excel Split Rows Where Columns Different

Excel Split Rows Where Columns Different

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: