Removes rows (based on condition) and copy cells (based on condition)

Question

I have to modify a huge VBA script, I am new to VBA and I must also say that I am not loving it :( I need some help writing an efficient For-loop, let me explain with an example.

During the first conversion I want to:

• copy the week cell into all empty cells until it reaches a new week
• copy the person name cell into all empty cells until it reaches a new person

During the second phase I want to:

• remove all rows where the cells C & D & E are empty

I tried the following code to remove rows if column C is empty and it works but I want to remove if column C & D & E are all empty.

On Error Resume Next
'ActiveSheet.Range(Columns(C), Columns(E)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete does not work
ActiveSheet.Columns(C).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

I would like to do this in place, I mean I want to see the result in place of the original cells.

How can cells be copied based on condition (e.g. copying week X) and how can row be deleted based on condition (e.g. if some cells are empty)?

Answer 1

I solved it myself.

' Copy week & name to all rows
For i = 1 To ActiveSheet.UsedRange.Rows.Count - 1 Step 1
    If IsEmpty(ActiveSheet.Range("A" & i + 1)) Then
        ActiveSheet.Range("A" & i).Copy Destination:=ActiveSheet.Range("A" & i + 1) ' Week
    End If
    If IsEmpty(ActiveSheet.Range("B" & i + 1)) Then
        ActiveSheet.Range("B" & i).Copy Destination:=ActiveSheet.Range("B" & i + 1) ' Name
    End If
Next i

' Delete entire rows where there is no number (this emplies the row is invalid)
On Error Resume Next
ActiveSheet.Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0