Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 9 at 14:55

This question came from our site for professional and enthusiast programmers.

    
what is your programmatic question? –  vba4all Apr 9 at 8:18
    
@mehow, see title: Removes rows (based on condition)? copy cells (based on condition)? I add this to question –  theAlse Apr 9 at 8:22
    
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 –  vba4all Apr 9 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. –  vba4all Apr 9 at 8:26
1  
@theAlse, please check this link: stackoverflow.com/questions/22722032/… I think it could give you a good start –  simoco Apr 9 at 8:55

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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