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:

|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONDUIT - PVC40 - EXPOSED
|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT 3/4" 2-H STRAP - PVC
|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" 2-H STRAP - PVC
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE# 2 THHN BLACK
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1E - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPSTYPE X4 - EM EXIT SIGN W/ PENDANT KIT
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A2 - 2X2 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPSWALL SCONCE NOT IN SCHEDULE
|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1 - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONDUIT - EMT
|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1E - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONN SS STL - EMT
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - EMT|| 0500 - LIGHT FIXTURES & LAMPS 1/2" 1-H STRAP - RMC - STEEL
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONN SS STL - EMT|| 0500 - LIGHT FIXTURES & LAMPS#12 THHN BLACK
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" COUPLING SS STL - EMT|| 1100 - GROUNDING 3/4" LOCKNUT - STEEL
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - RMC - GALV|| 1100 - GROUNDING 3/4" CONDUIT - PVC40 - EXPOSED
|| 1100 - GROUNDING 3/4" ELBOW 90 DEG - PVC40
|| 1100 - GROUNDING 3/4" COUPLING - PVC
|| 1100 - GROUNDING 3/4" ADAPTER MALE - PVC

[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>
[TD="align: center"]9[/TD]

[TD="align: right"]690[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]321[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]450[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]422[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]210[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]2,025[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

</tbody>
Comparison​


I would like it to look like this:


|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTT4 - 75KVA TRANSFORMER|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONDUIT - PVC40 - EXPOSED
|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" CONN FLEX DC SQUEEZE STRAIGHT|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT 3/4" 2-H STRAP - PVC
|| 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT1" 2-H STRAP - PVC
|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE# 2 THHN BLACK
|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)
|| 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPSTYPE X4 - EM EXIT SIGN W/ PENDANT KIT
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A1E - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPSWALL SCONCE NOT IN SCHEDULE
|| 0500 - LIGHT FIXTURES & LAMPSTYPE A2 - 2X2 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONDUIT - EMT
|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1 - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPS 1/2" CONN SS STL - EMT
|| 0500 - LIGHT FIXTURES & LAMPSTYPE B1E - 2X4 LED TROFFER|| 0500 - LIGHT FIXTURES & LAMPS 1/2" 1-H STRAP - RMC - STEEL
|| 0500 - LIGHT FIXTURES & LAMPS#12 THHN BLACK
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - EMT
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONN SS STL - EMT
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" COUPLING SS STL - EMT
|| 0600 - MOTOR/EQUIPMENT CONNECTIONS 1/2" CONDUIT - RMC - GALV
|| 1100 - GROUNDING 3/4" LOCKNUT - STEEL
|| 1100 - GROUNDING 3/4" CONDUIT - PVC40 - EXPOSED
|| 1100 - GROUNDING 3/4" ELBOW 90 DEG - PVC40
|| 1100 - GROUNDING 3/4" COUPLING - PVC
|| 1100 - GROUNDING 3/4" ADAPTER MALE - PVC

[TD="align: right"]60[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
</tr>
[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]690[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]321[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]450[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]422[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]2,025[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]210[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]

</tbody>
Comparison​
[TABLE="width: 85%"]
<tbody>[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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