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.

enter image description here

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
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)?

  • what is your programmatic question? – user222864 Apr 9 '14 at 8:18
  • @mehow, see title: Removes rows (based on condition)? copy cells (based on condition)? I add this to question – theAlse Apr 9 '14 at 8:22
  • 1
    are you looking for a macro? if so, you have to show us your current code. Tell us what does not work about it, error message etc. nobody should write the entire code for you, this is not what we do here at Stack Overflow. We help people get unstuck not do their homeworks – user222864 Apr 9 '14 at 8:23
  • To give you a hint... the second step can be achieved with a formula, just type =A2 in A3 and drag the formula down till next week. do that for both column A and B. Then 3rd step would be to iterate from row 1 to last row and clearContents of entire row when column D,E is empty. – user222864 Apr 9 '14 at 8:26
  • 1
    @theAlse, please check this link: stackoverflow.com/questions/22722032/… I think it could give you a good start – Dmitry Pavliv Apr 9 '14 at 8:55

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
On Error GoTo 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.