Need an Excel Guru quick....

[Columbo]

Right fellas I need help.

I have a massive file of data to process, there are around 79000 lines of data in this particular spreadsheet so a nice formula would help me here.

Basically a sample of the data is below and what I would like to do is where ever there is a 1 on the far right column, I need to insert automatically 2 rows directly below them. There are 6 columns in total.


H 42 13 03 A 1
H 42 14 01 A 0
H 42 14 01 B 0
H 42 14 01 C 0
H 42 14 02 A 0
H 42 14 02 B 0
H 42 14 02 C 0
H 42 14 03 A 1
H 42 15 01 A 0
H 42 15 01 B 0
H 42 15 01 C 0
H 42 15 02 A 0
H 42 15 02 B 0
H 42 15 02 C 0
H 42 15 03 A 1


Any formula to do this at all? MS help does'nt seem to return what I'm after so any help appreciated.

 

[nikebee]

If you add a new macro into excel you can use the following code

I'm looking at your data and presuming that your 1's and 0's are in cell F

If I'm incorrect just change

Set myRng = Range("F1")

To

Set myRng = Range("A1")
Set myRng = Range("B1")

etc...

    Dim myRng As Range
     
    Set myRng = Range("F1")
    While myRng.Value <> ""
        If myRng.Value = "1" Then
            myRng.EntireRow.Insert
            myRng.Offset(-1, 0) = ""
            Set myRng= myRng.Offset(1)
        End If
        Set myRng= myRng.Offset(1)
    Wend