Add n number of empty rows corresponding to cell value

[sameergaur]

Hi there,


Below is an example of the file I am working on. I want to add empty rows corresponding to the value in Col3, so 2 empty rows below ABC and 3 empty rows below ABD and so on.

[pre]

Col1    Col2    Col3
ABB
ABB     ABC	2
ABB     ABD	3

[/pre]
Can anyone help with that?

 

[Luke M]

This should do it.

[pre]

Sub addRows()
Dim lastRow As Long
Dim i As Long
Dim rowGrow As Integer
With ActiveSheet
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
Application.ScreenUpdating = False
For i = lastRow To 2 Step -1
rowGrow = Cells(i, 3).Value
If rowGrow <> 0 Then
Cells(i, 3).Offset(1, 0).Resize(rowGrow, 1).EntireRow.Insert
End If
Next
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sameergaur]

Thanks a lot Luke! That is exactly what I was looking for.

 

[Luke M]

Sweet, glad I could help.

 

[sameergaur]

I have a follow up question though. Below is the example:


1 ColA ColB ColC ColD ColE ColF

2 ABC X ABB

3 ABC X1 ABB ABC 2

4 ABD X1 ABB ABC 2 X

5 ABD X2 ABB ABC 2 X1

6 ABD X3 ABB ABD 3

7 ABE X ABB ABD 3 X1

8 ABE X4 ABB ABD 3 X2

9 ABB ABD 3 X3


I am trying to lookup multiple values corresponding to the value in ColD referring to ColA and ColB. Here is the formula that I am using:


=IF(ROWS(ColF$4:ColF4)<=$ColE$3,INDEX($ColA$2:$ColB$9,SMALL(IF($ColA$2:$ColA$9=$ColD$3,ROW($ColA$2:$ColA$9)-ROW($ColA$2)+1),ROWS(ColF$6:ColF6)),2),"")


The above formula works but I cannot extend it for the entire column as ColE and ColD values need to be static till it lookups all the values from ColB but they need to change to look up for the next values in ColE and ColD.


Any suggestions?


Thanks

Sameer

 

[Luke M]

Looking at your data in col D:F of your example, I think you could get the information you need much faster by just building a PivotTable with col A and then col B in the Row field areas. Are you just wanting the components of each main item in col A?

 

[sameergaur]

Pivot might not work as I am looking up the same value in multiple columns and in different formats. I am not able to put it in the right way, is there a way I could send you an example of the file?

 

[sameergaur]

I can't see anyway to attach it over here.

 

[NARAYANK991]

Hi Gaur ,


This forum does not have any way to attach files to posts.


What you can do is to upload your file to any file-sharing website ( Rapidshare , Hotfile , DropBox , GoogleDocs , SpeedyShare ,... ) , give others access to download , and post the access link here in this same topic.


Narayan