Thread: Excel question

Hi Rebelle,

This is the best i can come up with,. i've attached an example for you to look at,.. long story short,. just make sure the value is held in column D,.. obviously you can change the range though

Code:

    Dim lngRow As Long
    Dim intInsertRows As Integer
     
    Application.ScreenUpdating = False
    lngRow = 1
    Do Until IsEmpty(Range("D" & lngRow))
        If Range("D" & lngRow).Value > 1 Then
            intInsertRows = Range("D" & lngRow).Value - 1
            Range("D" & lngRow + 1 & ":D" & lngRow + intInsertRows).EntireRow.Insert
            Range("A" & lngRow & ":D" & (lngRow + intInsertRows)).FillDown
            Range("D" & lngRow & ":D" & (lngRow + intInsertRows)).Value = 1
            lngRow = lngRow + intInsertRows
        End If
        lngRow = lngRow + 1
     Loop
End Sub

Or you can simply use the "text to columns" feature of Excel. I believe you can find that under Data -> Text to Columns.

Originally Posted by BLaaaaaaaaaarche

Or you can simply use the "text to columns" feature of Excel. I believe you can find that under Data -> Text to Columns.

I can't see how that would work in this case,..

Out of curiosity i had a look into your sugestion but couldn't get the results as required above,. So for the moment lets assume i'm losing the plot,

I thought Text to Column command allows you to break text in one column into several columns using a delimiter such as <space> <hyphen> etc.

I.e "delimiter being <space> in this case"

Code:

Cel 1
Developer Barn

Into

Code:

Cel 1          Cell 2
Developer      Barn

With the data provided, all you have to do is the following:

1. Copy and paste the values into Excel.
2. Select column A.
3. Click on Data -> Text to Columns
4. Check 'Delimited'
5. Check 'Other' and enter in '-' as your delimiter.
6. Click next.
7. If data looks formatted correctly, click Finish.

I just did it and it works perfectly okay.

but what she wants to do ... is take this line in excel:

Code:

tool1 - 123 - so383-4 (no dash delimiters ... she was showing the columns)

and then paste it to 4 different lines in excel like so:

Code:

tool1 - 123 - so383 -1
tool1 - 123 - so383 -1
tool1 - 123 - so383 -1
tool1 - 123 - so383 -1

where the last number, in this case - 4, tells it how many times to write the data. i don't believe using your method will work for that.