Solved: Conditional Edit in Excel using VBA

[Stillwater]

Hello, I am trying to using VBA to do the following tasks. Column A named Category. Each time, if category changed, I want to add a row between the two category, and copy what is in the second category to column B of the new row, then I want to change the new row to color Grey.

Using an example: cell A4 is Class1, cell A5 is Class2. I need to add a new row between row 4 and row 5. Then I need copy Class2 to cell B5. (now the original row 5 is row 6). The last step is to change the color of row 5 to Grey.

Thank you very much for your help.

Regards,

StillWater

[mbarron]

Something like this perhaps?
Sub insert()
Dim lRow As Long, i As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = lRow To 3 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
'do nothing
Else
Cells(i, 1).EntireRow.insert
Cells(i, 2) = Cells(i + 1, 1)
Cells(i, 1).Resize(1, 2).Interior.Color = RGB(190, 190, 190)
End If
Next
End Sub

[Stillwater]

Hello, mbarron,

Thank you very much -- it works perfectly!

Stillwater:beerchug:

[Stillwater]

Hello, Last week, I posted to get help for adding extra rows when a different category is found. Mbrron's code works excellent -- see below.

Now, I need to add extra information for that. For each different category, there are several records. I need to summarize the information, and put them in the same row(the newly added row) under their current columns. For example, I need to count how many records for each category, the total cost of all records for each category.

Can you please give me more help?

Sincerely,

Thank you.

[mbarron]

If you can, please post an example workbook showing the before and after. You may have to do a dummy post or two before you can post an attachment.

[Stillwater]

Sample included. Left table is the original one and Right table is the after view. Basically, the table is sorted by Group and then by PA. If found a new Group, add a row and put the name there. Then for each PA within a group, only want to summarize the info. For example, only want to get the total number of projects for each PA, the average of Operations and the total of Billied_YTD. The extra rows will be deleted.

THank you again for your help.

Regards,


StillWater

[mbarron]

Try this one:
Sub reorg()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long
Dim x As Long, k As Integer
lRow = Cells(Rows.Count, 1).End(xlUp).Row
j = lRow
k = 1
For i = lRow To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
'do nothing
Else
Cells(i, 1).EntireRow.insert
Cells(i, 1) = Cells(i + 1, 1)
Cells(i, 1).Resize(1, 5).Interior.ColorIndex = 15
For x = j + 1 To i + 1 Step -1
Cells(x, 1) = ""
If Cells(x, 2) = Cells(x - 1, 2) Then
k = k + 1
Else
If k > 1 Then
Cells(x, 3) = k
Cells(x, 4) = WorksheetFunction.Average(Cells(x, 4).Resize(k, 1))
Cells(x, 5) = WorksheetFunction.Sum(Cells(x, 5).Resize(k, 1))
Cells(x + 1, 1).Resize(k - 1, 1).EntireRow.Delete
k = 1
Else
Cells(x, 3) = k
Cells(x, 4) = WorksheetFunction.Average(Cells(x, 4).Resize(k, 1))
Cells(x, 5) = WorksheetFunction.Sum(Cells(x, 5).Resize(k, 1))
End If
End If
Next
j = i - 1
End If

Next
Cells(1, 3) = "# of Project"
Application.ScreenUpdating = True
End Sub