Home excel macro to create new row for number diff
Reply: 0

excel macro to create new row for number diff

user8164
1#
user8164 Published in September 19, 2018, 7:06 pm

I need to take column A which has 2 values in a single cell and create a row for each number between the two values but keeping the rest of the columns the same as it was in the original row.

Here is my current macro.

Sub Macro1() ' ' Macro1 Macro '

' Dim CellValue As String strcellvalue = Replace(Range("A9").Value, " ", "") If InStr(strcellvalue, "-") Then Dim intCurrentBIN As Double Dim intLastBIN As Double Dim intMainRow As Integer Dim intNewRow As Integer intMainRow = 9 intNewRow = intMainRow + 1

    intCurrentBIN = CDbl(Left(strcellvalue, InStr(strcellvalue, "-") - 1))
    intLastBIN = CDbl(Right(strcellvalue, Len(strcellvalue) - InStr(strcellvalue, "-")))

        Do While intCurrentBIN < (intLastBIN + 1)
            Rows(intMainRow & ":" & intMainRow).Select
            Selection.Copy
            Rows(intNewRow & ":" & intNewRow).Select
            Selection.Insert Shift:=xlDown
            Range("A" & intNewRow).Select
            ActiveCell.FormulaR1C1 = intCurrentBIN
            intNewRow = intNewRow + 1
            intCurrentBIN = intCurrentBIN + 1
        Loop
    Rows(intMainRow & ":" & intMainRow).Select
    Selection.Delete
End If

End Sub

share|improve this question
  • The best way to get help here is to first try something. If you get stuck, do some research, make an attempt at fixing things yourself, and only then ask a specific question about your attempts. Questions asking for complete solutions without demonstrating research effort usually get downvoted and closed. – Jean-François Corbett Nov 20 '14 at 8:56
  • Seems you need to start looking into VBA and regular expressions. Regular expressions splitting up the text into numbers and then looping through "the_difference" times to print the number and copying description and code to a newly added line within the loop. – Robbert Koppenol Nov 20 '14 at 8:56
  • I have this so far which works on my example but not on my file itself. Will check and find if i can improve it. – user5469 Nov 20 '14 at 9:51

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.457316 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO