Need a macro to populate blank row by copying row below

[tsherv]

Hi, I have a excel file that looks like this:

Job# Customer Process
1 Customer A Process 1
1 Customer A Process 2
2 Customer B Process 1
3 Customer C Process 1
3 Customer C Process 2
3 Customer C Process 3

In my file, I've created a macro that inserts a blank row between change in job#. That part is working fine. Now I need to add to my macro to copy my job# and Customer name up to the blank row for each job. Ultimately my results would look like this:

Job# Customer Process
1 Customer A
1 Customer A Process 1
1 Customer A Process 2
2 Customer B
2 Customer B Process 1
3 Customer C
3 Customer C Process 1
3 Customer C Process 2
3 Customer C Process 3

How do I identify a blank row, and copy the cell contents below, up to the blank row?

Thanks,

Troy

 

[tsherv]

I found this code that does what I'm looking for, except it only handles one column at a time. What would I need to change to have it handle multiple columns in a blank row at the same time?

Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
LookIn:=xlFormulas).Row
For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(1).Value
Next
End Sub

 

[Fluff]

How about

Sub tsherve()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      With .SpecialCells(xlBlanks)
         .FormulaR1C1 = "=r[1]c"
         .Offset(, 1).FormulaR1C1 = "=r[1]c"
      End With
      .Resize(, 2).Value = .Resize(, 2).Value
   End With
End Sub

 

[tsherv]

How about

Sub tsherve()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      With .SpecialCells(xlBlanks)
         .FormulaR1C1 = "=r[1]c"
         .Offset(, 1).FormulaR1C1 = "=r[1]c"
      End With
      .Resize(, 2).Value = .Resize(, 2).Value
   End With
End Sub

Thank you so much! I'm very close. The code above works perfect for columns A and B. What if I wanted to expand it, can copy columns A - F and also columns J and L?

 

[Fluff]

How about

Sub tsherve()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      With .SpecialCells(xlBlanks)
         Intersect(.EntireRow, Range("A:F, J:L")).FormulaR1C1 = "=r[1]c"
      End With
      .Resize(, 12).Value = .Resize(, 12).Value
   End With
End Sub

 

[tsherv]

This works great! One more question (I'm getting greedy): In addition to the code above, would it also be possible to put a static value (text) in column N and Column O of the blank row?

 

[Fluff]

Like

Sub tsherve()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      With .SpecialCells(xlBlanks)
         Intersect(.EntireRow, Range("A:F, J:L")).FormulaR1C1 = "=r[1]c"
         Intersect(.EntireRow, Range("N:O")).Value = Array("Fluff", "tsherv")
      End With
      .Resize(, 12).Value = .Resize(, 12).Value
   End With
End Sub

 

[tsherv]

Thanks Fluff!! Works great.

 

[Fluff]

You're welcome & thanks for the feedback.