Add blank line after cell value has changed

richertt

New Member
Joined
Sep 1, 2018
Messages
11
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,598

ADVERTISEMENT

Oops! Fluff has already answered. :)
 
Last edited:

richertt

New Member
Joined
Sep 1, 2018
Messages
11
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

richertt

New Member
Joined
Sep 1, 2018
Messages
11
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:confused:

Troy
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

New Member
Joined
Sep 1, 2018
Messages
11
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,401
Members
409,871
Latest member
i1patrick
Top