Macro

Closed
Report
Posts
2
Registration date
Monday April 29, 2013
Status
Member
Last seen
April 30, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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..



5 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
53
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.
Posts
2
Registration date
Monday April 29, 2013
Status
Member
Last seen
April 30, 2013

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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
53
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.
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
53
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.