How to write VBA program to do the specific tasks

[Archimedes79]

Hi,
I would like to know how to insert a new row if the content in the cell in the particular column is different value. Attached is the example image of worksheet.
If the value in the adjacent row in the colum E is different value, I want to insert a new row. How to write a program in VBA?

Book1.jpg is the raw file.
What I want to do?
1) I want to sort the values in column E from lowest value to highest value. (Respective entries from column A,B,C and D must follow to the value in column E)
2) After that I want to insert a new role if the adjacent entries in column E are not equal. (Please refer to Book2.jpg)

Book2.jpg is the format I want.

Thanks in advance

 

[p45cal]

 

[Archimedes79]

How to modify the code if I want to delete column C,D and E after inserting rows?

 

[Belleke]

add this before end sub
Columns("C:E").EntireColumn.Delete

 

[Archimedes79]

Hi all,
Thank you all for the solution.

If I use the code below for Book3.xlsx, The very first two rows are not correct as seen in the book4.xlsx. There should be an empty row between B1 and B2 as the initial values in respective H entries are not equal.

This is the code:

Sub MC_Table()
With Range("A1").CurrentRegion.Columns("A:H")
  .Sort Range("H1"), xlAscending, Header:=x1Yes
  For rw = .Cells(.Cells.Count).Row To 3 Step -1
  If .Cells(rw, "H").Value <> .Cells(rw - 1, "H") Then Rows(rw).Insert
  Next rw
End With
Columns("E:H").EntireColumn.Delete
Columns("A:B").EntireColumn.Delete
End Sub

 

[p45cal]

x1Yes should be xlYes