VBA code to create duplicate lines

jeskit · December 20th, 2011, 10:48 AM

Hi

I was wondering if anyone could help me with a vba macro i am trying to build.

Currently I have a workbook with table which looks like this:

Row1 1SPACE1SPACE1
Row2 1
Row3

However I need to macro make the table look like this:

Row1 1
Row1SPACE1
Row1SPACE SPACE1
Row2 1

What I need the macro to do is say if the cells in columns 2-4 are empty then delete the row. If the cells in columns 2-4 only have one cell with data ignore it. If multiple cells in columns 2-4 only have data then create a new line so that each number on that row has its own line.

I have managed to create a piece of code which looks at whether the row is empty however I can seem to find a way of doing the rest. The code i have created needs to have variables for the row number as this is not always the same. Currently the code i have is:

Code:

    StartLine = Sheets("Sheet1").Range("B24").Value
    
    EndLine = Sheets("Sheet1").Range("B25").Value

    For i = EndLine To StartLine Step -1

    Cells(i, 9).Select

    If Cells(i, 9) = "" Then
                    
        If Cells(i, 10) = "" Then
        If Cells(i, 11) = "" Then
        If Cells(i, 12) = "" Then
        If Cells(i, 13) = "" Then
                           
            Cells(i, 13).EntireRow.Delete
                    
        End If
        End If
        End If
        End If
        
    End If

Can anyone help me??

Thanks

Jeskit