Copy value into next blank cell in a column, on a loop

[tonybr]

Hi

Can someone assist with the required VB for this please?

In the example below, there are areas with subtotals but I need the Area description to be copied next to the sub total.

e.g. the string "Area A" needs to be copied from C2 to the next empty cell, C9. "Area B" needs to be copied from C10 to C17 etc, until all areas have been copied.

Thanks

 

[Osvaldo Palmeiro]

Hi. Try this code, please.

Sub CopyIntoNextBlank()
 Dim r As Range, LR As Long
  LR = Cells(Rows.Count, 5).End(3).Row + 1
  For Each r In Range("E2:E" & LR).SpecialCells(2).Areas
   r.Cells(1, 1).Offset(r.Count - 1, -2) = r.Cells(1, 1).Offset(-1, -2)
  Next r
End Sub

 

[tonybr]

Perfect! Thanks so much.