Excel - How to insert rows when criteria is met?

December 2016




Issue


I have two tables. If a field in table A is chosen, I want a macro which will insert rows of the data listed in table B into a new sheet.

For instance:
Column Table A Column Table B
Apple Mac 
Apple Green 
Apple Bartlett 

Pear 1 
Pear 2 
Pear 3 


If I chose Apple, i want it in a new sheet :
Row 1 Mac 
Row 2 Green 
Row 3 Barlett 


Thanks in advance for your help.

Solution


Introduce row1 and give column headings, suppose this is in sheet1

fruit type
Apple Mac
Apple Green
Apple Bartlett
  • Right click this sheets tab and click view code

in the resulting window copy this "event code".


Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
Dim dest As Range, cfind As Range, x As String 
Dim j As Integer, y As String, add As String 
If Target.Column <> 1 Then Exit Sub 
x = Target.Value 
Set cfind = Cells.Find(what:=x) 
j = cfind.Row 
x = cfind.Offset(0, 1).Value 
add = cfind.Address 
With Worksheets("sheet2") 
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest = "row" & " " & j 
dest.Offset(0, 1) = x 
End With 
Do 
Set cfind = Cells.FindNext(cfind) 
If cfind Is Nothing Then Exit Do 
If cfind.Address = add Then Exit Do 
j = cfind.Row 
x = cfind.Offset(0, 1).Value 
With Worksheets("sheet2") 
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest = "row" & " " & j 
dest.Offset(0, 1) = x 
End With 
Loop 
End Sub 
  • Now just click apple in sheet 1 and see what you get in sheet 2


The macro undo clears sheet2 so that you can again choose "apple" and
recheck .


Sub undo() 
Worksheets("sheet2").Cells.Clear 
End Sub

Note


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - How to insert rows when criteria is met? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.