Add blank line after cell value has changed

[richertt]

Hi Guys,

I have a timeclock dump of data where column "A" = a person's name and column "B" is their clock in / clock out times. I want to add a blank line between each group of names. Searching google, I found this bt of code...

Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "ADD A LINE"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.count To 2 Step -1
If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
WorkRng.Cells(i, 1).EntireRow.Insert
End If
Next
Application.ScreenUpdating = True

This works great except it prompts me to select the column to make it work (column A). The names are always in column A. How do I modify the code to automatically select column "A"?

Thanks in advance,

Troy

 

[Fluff]

How about

Sub AddRows()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 1) <> Cells(i - 1, 1) Then Rows(i).Insert
   Next i
End Sub

 

[richertt]

Thank you! That works perfectly!

 

[Fluff]

Glad to help & thanks for the feedback

 

[mumps]

Oops! Fluff has already answered.

 

[richertt]

Hello Guys and Gals,

Fluff fixed my first problem. Hopefully someone can help me with my second challenge....

What if I wanted to change the color of the cells in that extra row to green, and have the green format go out to the last column with data on it? Also, what if I needed to add an additional line below the the "green line"?

Troy

 

[Fluff]

This sounds like mission creep , any other requirements?
Also what is the last used column?

 

[richertt]

Hey Fluff,

I'm am really trying to figure this stuff out on my own. I feel bad having to bother you ..... I guess I should have given all of the information at once.

Requirements;

* Two blank lines with the bottom line being green. Green would go out to column "L"
* On the first blank line, I would like to sum column "F" and that would repeat on every grouping that has the two blank lines.

I'm sorry to keep bothering you with my problems

Troy

 

[Fluff]

How about

Sub AddRows()
   Dim i As Long, j As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 1) <> Cells(i - 1, 1) Then
         Rows(i).Resize(2).Insert
         Cells(i + 1, 1).Resize(, 12).Interior.Color = 45678
         If j <> 0 Then Cells(j, 6).Formula = "=sum(" & Range("F" & i + 2).Resize(j - i - 2).Address & ")"
         j = i + 2
      End If
   Next i
End Sub

 

[richertt]

OMG, that would have taken me months to figure out! One last challenge and I promise I will leave you alone.... In the very last grouping, I don't get the extra two lines with one of them being green. I also don't get the formula. What do I need to modify in the code to include the very last grouping?