Macro Closed

Question

Hello, 

I need to insert  a multiple rows according to the count mentioned in column d.

for example column A,B,C,D contains the data in Column D the number of rows should be added will be mentioned (the A column B column would be unique so that it should be automatically entered in all inserted columns which was mentioned in D...

For ex in column D it mentioned as 6 means 6 rows has to be added and it mentioned 3 then 3 rows should be added..

Is it possible... much appreciated to help in this..



Configuration: Windows XP / Internet Explorer 8.0

Zohaib R · Answer

Hi Lalir,

A little clarification will help here. From your post it is not clear as to how the arrangement will look. You have mentioned that Column D will have a number. Is it any specific cell in the Column D which will hold the Number (such as D1, D2, D3, etc.)? Also, you have mentioned the rows to be inserted in column A, B and C, however you have not mentioned as to from which row the insert process should begin.

Please reply with the above details so that I can find a solution.

Lalir · Answer

Hi,

The excel sheet formate is like below..

Raw Data		
Header 1	Header 2	Header 3
A	123	741,752,125,496
B	456	120,325
C	789	480,741,456,896,921
		
Output Data (The formate i required like below)		
		
Header 1	Header 2	Header 3
A	123	741
A	123	752
A	123	125
A	123	496
B	456	120
B	456	325
C	789	480
C	789	741
C	789	456
C	789	896
C	789	921

is it possble to get like this

Zohaib R · Answer

Hi Lalir,

A small change is required in the data present under "Header 3". "741,752,125,496" should be in the format "741, 725, 125, 496" (separated by commas with a spaces). Then you can run the following macro to accomplish the task:

    Range("C2").Select
    Range("C2:C1048575").Select
    Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
    Range("C2").Select
    Range("A1:C1").Select
    Selection.Copy
    Range("A6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2:F2").Select
    Selection.Copy
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A2:B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7:B10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C3:D3").Select
    Selection.Copy
    Range("C11").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A3:B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A11:B12").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C4:G4").Select
    Selection.Copy
    Range("C13").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A4:B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A13:B17").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C17").Select

If you have difficulty creating a Macro, please check the below mentioned article:

http://office.microsoft.com/en-in/excel-help/create-a-macro-HP005204711.aspx

Please revert for clarification.

Lalir · Answer

Hi I got the Syntax error

I have save this vb in my excel n name it as Annual and also i have name this macro as annual.

But i got the syntax error in Sub Annual() 

Sub Annual()

Range("C2").Select

    Range("C2:C1048575").Select

    Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _

        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _

        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _

        TrailingMinusNumbers:=True

    Range("C2").Select

    Range("A1:C1").Select

    Selection.Copy

    Range("A6").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("C2:F2").Select

    Selection.Copy

    Range("C7").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Range("A2:B2").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("A7:B10").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("C3:D3").Select

    Selection.Copy

    Range("C11").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Range("A3:B3").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("A11:B12").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("C4:G4").Select

    Selection.Copy

    Range("C13").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Range("A4:B4").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("A13:B17").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("C17").Select


End Sub

Zohaib R · Answer

Hi Lalir,

I have uploaded the sample file with working Macro on a file sharing website, please download the file from the below mentioned link:

http://speedy.sh/jGf6E/TextToColumnsToRows.xlsm

Check if this works and reply with results.

Macro

5 responses

Similar discussions

Subscribe To Our Newsletter!