Find missing member from list and add a blank row there

wapcoslucknow

New Member
Joined
Oct 17, 2020
Messages
13
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
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Show your code as you have it here.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144
I think the best chance of getting a working solution is to upload a copy of your file as I suggested in Post #2.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144

ADVERTISEMENT

Please review Post #2 for instructions on how to upload your file.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144

ADVERTISEMENT

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).
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
And suddenly there was an extra Column.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,009
Messages
5,599,326
Members
414,305
Latest member
scarletX

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
Top