Previous Topic Next Topic

Macros to Insert rows defined in column "X"

Closed
akidivecha Posts 3 Registration date Sunday 11 August 2013 Status Member Last seen 12 August 2013 - 11 Aug 2013 à 10:57
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 12 Aug 2013 à 11:24
Hi,

I m new to macros, need your help to create macro per my below requirement...

Eg.. Value updated in column "A" is 10 so it should add 10 new rows below.

Tks, Aki

5 responses

Réponse 1 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
12 Aug 2013 à 01:29
suppose data in sheet 1 is like this

HDNG1
2
X
3
X
5
X
X

copy data in sheet 1 to sheet 2 to,reserve origial data

try this macro and check and confirm 

Sub TEST()
Dim j As Integer, k As Integer, m As Long
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
Worksheets("sheet1").Activate
j = Range("A1").End(xlDown).Row
For k = j To 2 Step -1
If IsNumeric(Cells(k, 1)) Then
m = Cells(k, 1).Value
Range(Cells(k, 1).Offset(1, 0), Cells(k, 1).Offset(m, 0)).EntireRow.Insert
End If
Next k
End Sub


for deleting or inserting row start from last row to first data row
Réponse 2 / 5
akidivecha Posts 3 Registration date Sunday 11 August 2013 Status Member Last seen 12 August 2013
12 Aug 2013 à 01:43
Tks for your reply, but not clear with procedure.
can you help me to provide link wherein i can upload my document for better understanding.

Tks
Réponse 3 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
12 Aug 2013 à 02:51
"test" is a macro

do you know how to introduce a macro in your file

close all other excel files except this one.
Ipresume your version is 2007 or later

1. click developer ribbon
2. click visual basic editor on the left end
3.cliclk control+R
4.you see all the open files
5. highlight your file and click insert menu and click module
6. module window will be on your right
7. in that module window copy paste the macro till end sub
8. keep the cursor within the macro test in the module
9. clcik RUN on the right or hit function key F5
10. if you at to see what each code line does then click consecutively F8 till orange color disappears.

I presumed you do not know the procedure how to enter the macro and run it.
if it something else then revert back to newsgroup
Réponse 4 / 5
akidivecha Posts 3 Registration date Sunday 11 August 2013 Status Member Last seen 12 August 2013
12 Aug 2013 à 03:32
Hi venkat,

Tks again for reply, Followed your inst but getting below pop-up error while run the macro..

Run Time Error "6" Overflow
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
12 Aug 2013 à 11:24
Hi Akidivecha,

You probably have more then 32.767 rows of data. If this is the case then remove " As Integer" 2 times from the second code line.

Best regards,
Trowa
Réponse 5 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
12 Aug 2013 à 03:44
open
speedyshare.con
uplodad your file and give the web address for DOWNLOADING


is your data is in the same configuration as mine