Insert Number of Excel rows based on a column 1

[djieon]

Hi all,

I have an excel sheet that looks like the following:-

Acacia Avenue

​

2
Beech Street

​

6
High Street

​

10

What I want to be able to do is create a worksheet that creates 2 rows for Acacia Avenue, 6 rows for Beech Street, and 10 for High Street

Is this easy to do? If so how!?!?

Thanks in advance

David.

 

[strongm]

Are you automating this from VB6. Or running VBA in Excel? if the latter, then you probably want to ask this in forum707

 

[Andrzejek]

Starting point:

[pre]
Sheet1
A
Acacia Avenue
2
Beech Street
6
High Street
10
[/pre]
Code in Excel:

Option Explicit

Sub Macro1()

Dim intRow As Integer
Dim strStreet As String
Dim intNo As Integer

intRow = 1

Do While Sheet1.Range("A" & intRow).Value <> ""

    If IsNumeric(Sheet1.Range("A" & intRow).Value) Then
        Call PlaceInfo(strStreet, Sheet1.Range("A" & intRow).Value)
    Else
        strStreet = Sheet1.Range("A" & intRow).Value
    End If

    intRow = intRow + 1
Loop

End Sub

Private Sub PlaceInfo(ByRef strS As String, ByRef intN As Integer)
Dim i As Integer
Static intR As Integer
If intR = 0 Then intR = 1

For i = 1 To intN
    Sheet2.Range("A" & intR).Value = strS
    intR = intR + 1
Next i

End Sub

Result:

[pre]
Sheet2
A
Acacia Avenue
Acacia Avenue
Beech Street
Beech Street
Beech Street
Beech Street
Beech Street
Beech Street
High Street
High Street
High Street
High Street
High Street
High Street
High Street
High Street
High Street
High Street
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

I guess my solution is NOT what David wants...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

@Andy, if you notice, the OP seems to not to have a good record of replying to his own threads or any other threads.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]