Insert Partial Blank Rows

Status
Not open for further replies.

chethead

New Member
Joined
Jul 23, 2015
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a worksheet, "Comparison" that is fed by two other worksheets. This worksheet compares two different estimates, original and new. What I would like to do is add blank cells in either A:E or J:N so that my systems numbers in cells A and J match. I don't need anything else to match, just the A and J. My current Comparison worksheet looks like this:

9|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL6909.009.00|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONDUIT - PVC40 - EXPOSED409.009.00
10|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)19.009.00|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT 3/4" 2-H STRAP - PVC219.009.00
11|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW19.009.00|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" 2-H STRAP - PVC149.009.00
12|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER829.009.00|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE# 2 THHN BLACK3219.009.00
13|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1E - 2X4 LED TROFFER179.009.00|| 0500 - LIGHT FIXTURES & LAMPSTYPE X4 - EM EXIT SIGN W/ PENDANT KIT219.009.00
14|| 0500 - LIGHT FIXTURES & LAMPSTYPE A2 - 2X2 LED TROFFER29.009.00|| 0500 - LIGHT FIXTURES & LAMPSWALL SCONCE NOT IN SCHEDULE49.009.00
15|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1 - 2X4 LED TROFFER129.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONDUIT - EMT4509.009.00
16|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1E - 2X4 LED TROFFER69.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONN SS STL - EMT4229.009.00
17|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - EMT2109.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" 1-H STRAP - RMC - STEEL489.009.00
18|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONN SS STL - EMT289.009.00|| 0500 - LIGHT FIXTURES & LAMPS#12 THHN BLACK2,0259.009.00
19|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" COUPLING SS STL - EMT149.009.00|| 1100 - GROUNDING 3/4" LOCKNUT - STEEL89.009.00
20|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - RMC - GALV1009.009.00|| 1100 - GROUNDING 3/4" CONDUIT - PVC40 - EXPOSED1009.009.00
21|| 1100 - GROUNDING 3/4" ELBOW 90 DEG - PVC4089.009.00
22|| 1100 - GROUNDING 3/4" COUPLING - PVC169.009.00
23|| 1100 - GROUNDING 3/4" ADAPTER MALE - PVC89.009.00

[TD="align: right"]4[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT[/TD]
[TD] 3/4" CONDUIT - PVC40 - EXPOSED[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
</tr>
</tbody>
Comparison


I would like it to look like this:


9|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTT4 - 75KVA TRANSFORMER19.009.00|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONDUIT - PVC40 - EXPOSED409.009.00
10|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONN FLEX DC SQUEEZE STRAIGHT49.009.00|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT 3/4" 2-H STRAP - PVC219.009.00
11|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" 2-H STRAP - PVC149.009.00
12|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL6909.009.00|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE# 2 THHN BLACK3219.009.00
13|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)19.009.00
14|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW19.009.00
15|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER829.009.00|| 0500 - LIGHT FIXTURES & LAMPSTYPE X4 - EM EXIT SIGN W/ PENDANT KIT219.009.00
16|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1E - 2X4 LED TROFFER179.009.00|| 0500 - LIGHT FIXTURES & LAMPSWALL SCONCE NOT IN SCHEDULE49.009.00
17|| 0500 - LIGHT FIXTURES & LAMPSTYPE A2 - 2X2 LED TROFFER29.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONDUIT - EMT4509.009.00
18|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1 - 2X4 LED TROFFER129.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONN SS STL - EMT4229.009.00
19|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1E - 2X4 LED TROFFER69.009.00|| 0500 - LIGHT FIXTURES & LAMPS 1/2" 1-H STRAP - RMC - STEEL489.009.00
20|| 0500 - LIGHT FIXTURES & LAMPS#12 THHN BLACK2,0259.009.00
21|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - EMT2109.009.00
22|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONN SS STL - EMT289.009.00
23|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" COUPLING SS STL - EMT149.009.00
24|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - RMC - GALV1009.009.00
25|| 1100 - GROUNDING 3/4" LOCKNUT - STEEL89.009.00
26|| 1100 - GROUNDING 3/4" CONDUIT - PVC40 - EXPOSED1009.009.00
27|| 1100 - GROUNDING 3/4" ELBOW 90 DEG - PVC4089.009.00
28|| 1100 - GROUNDING 3/4" COUPLING - PVC169.009.00
29|| 1100 - GROUNDING 3/4" ADAPTER MALE - PVC89.009.00

[TD="align: right"]60[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
</tr>
</tbody>
Comparison
It looks like the black border lines showed up at the start of a new system. They separate the different systems and I would like them to be red if possible. I will create a button to activate the macro.

Any help would be greatly appreciated!

Thank you

<tbody>
</tbody>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Try this:

Code:
[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1114665a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1114665-insert-partial-blank-rows.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] a [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

a = [COLOR=Darkcyan]"A"[/COLOR]
b = [COLOR=Darkcyan]"J"[/COLOR]

Application.ScreenUpdating = False
n = Range(a & [COLOR=Darkcyan]":"[/COLOR] & b).Find([COLOR=Darkcyan]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[COLOR=Royalblue]For[/COLOR] i = [COLOR=Brown]2[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Brown]2[/COLOR] * n
    [COLOR=Royalblue]If[/COLOR] Cells(i, a) = [COLOR=Darkcyan]""[/COLOR] [COLOR=Royalblue]And[/COLOR] Cells(i, b) = [COLOR=Darkcyan]""[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    [COLOR=Royalblue]If[/COLOR] Cells(i, a) < Cells(i, b) [COLOR=Royalblue]Then[/COLOR]
        Cells(i, b).Resize([COLOR=Brown]1[/COLOR], [COLOR=Brown]5[/COLOR]).Rows.Insert xlDown
    [COLOR=Royalblue]ElseIf[/COLOR] Cells(i, a) > Cells(i, b) [COLOR=Royalblue]Then[/COLOR]
        Cells(i, a).Resize([COLOR=Brown]1[/COLOR], [COLOR=Brown]5[/COLOR]).Rows.Insert xlDown
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
[COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

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