Insert Partial Blank Rows

Status
Not open for further replies.
C

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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]
 
Upvote 0
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,215,730
Messages
6,126,528
Members
449,316
Latest member
sravya

Share this page

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