Macros to Insert rows defined in column "X"

Closed
akidivecha
Posts
3
Registration date
Sunday August 11, 2013
Status
Member
Last seen
August 12, 2013
- Aug 11, 2013 at 10:57 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Aug 12, 2013 at 11:24 AM
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 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 12, 2013 at 01:29 AM
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
1
akidivecha
Posts
3
Registration date
Sunday August 11, 2013
Status
Member
Last seen
August 12, 2013

Aug 12, 2013 at 01:43 AM
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 12, 2013 at 02:51 AM
"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
0
akidivecha
Posts
3
Registration date
Sunday August 11, 2013
Status
Member
Last seen
August 12, 2013

Aug 12, 2013 at 03:32 AM
Hi venkat,

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

Run Time Error "6" Overflow
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 12, 2013 at 11:24 AM
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
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 12, 2013 at 03:44 AM
open
speedyshare.con
uplodad your file and give the web address for DOWNLOADING


is your data is in the same configuration as mine
0