Find missing member from list and add a blank row there

wapcoslucknow

New Member
Joined
Oct 17, 2020
Messages
14
Office Version
  1. 2007
Platform
  1. Windows
Hello,

I need a very special answer from you, since I am not fluent in VB, I am not able to sole it.

I have a group (slave group) in Excel like: -

HT line length0.01
HT Poles4
LT Line Length0.56
LT Poles10
DTR 25 KVA2
BPL118

And a master group like: -

HT line length
HT Poles
LT Line Length
LT Poles
DTR 10 KVA
DTR 16 KVA
DTR 25 KVA
DTR 63 KVA
BPL

Master group has 9 elements, whereas sub group can have any number of elements from these 9 items (maximum 9). Here, 6 members out of 9.
My requirement is arranging sub group like: -

HT line length0.01
HT Poles4
LT Line Length0.56
LT Poles10
DTR 10 KVA
DTR 16 KVA
DTR 25 KVA2
DTR 63 KVA
BPL118

Here, blank rows are inserted, as per master group i.e. master group has to be followed and blank row should be inserted at missing points.
Also there will be so many sub group in the same sheet (one after other downwards)
I hope you understand my question.

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I think the best chance of getting a working solution is to upload a copy of your file as I suggested in Post #2.
 
Upvote 0
Please review Post #2 for instructions on how to upload your file.
 
Upvote 0
What you posted is a screen shot of a portion of your sheet which isn't really helpful. Below are the instructions for uploading your file.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Change references, like sheet names, as required
Code:
Sub AAAAA_3_Sheet3()
Dim myAreas As Areas, myAreas_2 As Areas
Dim a, i As Long, ii As Long, j As Long
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Application.ScreenUpdating = False
a = Range("A1:A" & lr).Value
Set myAreas = ActiveSheet.Columns(4).SpecialCells(2).Areas
    For i = 1 To myAreas.Count
        Cells(Rows.Count, lc + 100).End(xlUp).Offset(2).Resize(lr).Value = a
    Next i
    Cells(1, lc + 100).Resize(2).Delete Shift:=xlUp
Set myAreas_2 = ActiveSheet.Columns(lc + 100).SpecialCells(2).Areas
    For ii = 1 To myAreas_2.Count
        With Range(myAreas_2(ii).Address)
            For j = 1 To lr
                On Error Resume Next
                    myAreas_2(ii).Cells(j).Offset(, 1).Resize(, 2).Value = Range(myAreas(ii).Address).Find(myAreas_2(ii).Cells(j)).Offset(, 1).Resize(, 2).Value
                On Error GoTo 0
            Next j
        End With
    Next ii
With Range(Cells(1, lc + 100), Cells(Cells(Rows.Count, lc + 100).End(xlUp).Row, lc + 100)).Resize(, 3)
    .Copy Cells(1, 4)
    .ClearContents
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top