Noob Question

neetusxm

New Member
Joined
Jan 6, 2010
Messages
1
Hello everyone, i am a novice excel user and i need help with the following:-

I have a long data sheet and i need to make more rows of specific number for each rows that i have for eg.

<TABLE style="WIDTH: 221pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=294><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=102>Model Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=77>Item</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=51>Cost</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=64>Stock Pcs</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BR-101</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Brown Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$50.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BL-102</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Black Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$65.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>CR-103</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Cream Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$35.65 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BR-104</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Brown Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$88.98 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>2</TD></TR></TBODY></TABLE>

So i need to automatically create 10 rows of same data of model BR-101 and so on....

Is this possible in excel?

Again many thanks for any help i get in this

:)
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
neetusxm,

Welcome to the MrExcel board.


Sample data before the macro:


Excel Workbook
ABCD
1Model NumberItemCostStock Pcs
2BR-101Brown Sofa$50.0010
3BL-102Black Sofa$65.005
4CR-103Cream Sofa$35.658
5BR-104Brown Sofa$88.982
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sheet1





After the macro:


Excel Workbook
ABCD
1Model NumberItemCostStock Pcs
2BR-101Brown Sofa$50.0010
3BR-101Brown Sofa$50.0010
4BR-101Brown Sofa$50.0010
5BR-101Brown Sofa$50.0010
6BR-101Brown Sofa$50.0010
7BR-101Brown Sofa$50.0010
8BR-101Brown Sofa$50.0010
9BR-101Brown Sofa$50.0010
10BR-101Brown Sofa$50.0010
11BR-101Brown Sofa$50.0010
12BR-101Brown Sofa$50.0010
13BL-102Black Sofa$65.005
14BL-102Black Sofa$65.005
15BL-102Black Sofa$65.005
16BL-102Black Sofa$65.005
17BL-102Black Sofa$65.005
18BL-102Black Sofa$65.005
19CR-103Cream Sofa$35.658
20CR-103Cream Sofa$35.658
21CR-103Cream Sofa$35.658
22CR-103Cream Sofa$35.658
23CR-103Cream Sofa$35.658
24CR-103Cream Sofa$35.658
25CR-103Cream Sofa$35.658
26CR-103Cream Sofa$35.658
27CR-103Cream Sofa$35.658
28BR-104Brown Sofa$88.982
29BR-104Brown Sofa$88.982
30BR-104Brown Sofa$88.982
31
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub CopyRows()
' hiker95, 20100106
Dim a As Long, b As Long, rng As Range
Application.ScreenUpdating = False
For a = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
  Set rng = Range("A" & a & ":D" & a)
  b = Range("D" & a).Value
  Range("A" & a & ":A" & a + b - 1).EntireRow.Insert
  rng.Copy Range("A" & a & ":D" & a + b - 1)
Next a
Application.ScreenUpdating = True
End Sub

Then run the "CopyRows" macro.
 

Forum statistics

Threads
1,085,340
Messages
5,383,050
Members
401,813
Latest member
Lucy_Wood

Some videos you may like

This Week's Hot Topics

Top