Solved: Multiplying cells

[alex108]

This is probably fairly simple (but not for me). I am making a bill of materials up that copies data to a "test" worksheet. Depending on the items copied I end up with quite a few empty rows interspersed throughout the worksheet- maximum rows is 200.
Column "c" contains the item quantity and column "d" contains the price.
Right now I use a sum function in column "e" in the "test" worksheet to get total cost of the items, but when I run the macro to clear the empty rows (say I end up with 150 rows with data) it erases my functions in them also, so that when I run it again there are no sum functions below row 150.
I am trying to do it in vba but am totally lost in formulas etc. My first post so please excuse it if I am not following convention etc.

[Bob Phillips]

Can you post your workbook, it is difficult to envisage.

[alex108]

This is the raw "test" worksheet. I need column c price to multiply by column d quantity to give total item price in column e.
Right now I do this with sum function in column e in the "test" worksheet. You can see all the empty rows in this sheet. When I run the macro to clear the empty cells the sum funciton in those rows is cleared also, so the next time I run the sheet there are no formulas. I think the solution is not to have the sum function in the test sheet but to clear the empty rows then have a macro to the multiplication for the cells. I hope that makes more sense?

12389.82VALVE HIGH LEVEL SHUT-OFF ANGLE VALVE 2"100-01-21D$212.761212.76 $0.0010X46A STRAINERC-2891E$19.44119.44X101 VALVE POSITION IND.C-8972G$58.32158.32NIBCO,2",DUCTILE IRON,SWING CHECK VALVE,FLANGED,R/FF938-31 2"$359.101359.1VALVE CAST IRON,SWING CHECK 2" F918-B 2"$123.361123.36LINE STRAINER 2"286-1000-AS$102.431102.43Starter-NEMA 9 Size "1" Start/Stop14DSF32HA-A4$750.00175002K3-X2 G-R 2"X2"02K3-X2 3P$1,063.1911063.190w/2HP,3PH,3450RPM EXPLOSION PROOF MOTOR1000000000011" COUPLING GUARDFTI-700023$25.00125MOTOR BASE$200.001200VENT FIRE - 8"202F-0600$164.642329.28INTERSTITIAL MONITOR K-2-120FTI-318017$39.75139.7500000000700SPA METER & REGISTER, (1/10 USG), w/AIR ELIMINATOR/FTI-255006$1,728.6411728.64VALVE BUTTERFLY WET/DRY 2", BUNAWD201ALB$33.98133.98GAUGE DIFFERENTIAL PRESSURE (1002-S1009)FTI-318005$45.00290MILLIPORE TEST KITKIT-5$39.37278.740VENT 2" OUTBREATHING354-0200-AV$8.6718.67VALVE BUTTERFLY 2" BUNALD2100-3 2"$73.443220.322" FLOAT & SUCTION W/O SWIVELFTI-313021$330.911330.91SWIVEL-2" ALUM FOR SUBMERGE SERVICE (DOUBLE) FLANGED3640-FJ0202$277.801277.8FILTERVF-61EP 1/2$159.002318ELEMENTACO-51201L$52.572105.14ADAPTER-2" ALUMINUMPART F 20F$5.6815.68DUST CAP-2" LOCKING (MODIFIED)20VL-MODIFIED$23.05123.05ADAPTER-4" ALUMINUMPART A 40A$18.41118.41DUST CAP-4" LOCKINGPART VL 40VL$23.34123.34000000000000126" SIDE MOUNT CATWALK W/LADDERFTI-313010$2,124.6812124.68DOOR, POK ROLL-UP, 78" X 84", ALU 30 EZY, W/DRILLED MOUNTING HOLES, CYLINDER STYLE B HANDLE W/LOCK, IDENTICAL KEYS, ANODIZED SATIN FINISH AND REINFORCED EZY TRACKFTI-700004$1,143.5011143.5EAVE VENT, 16" X 8", (ORDER WHEN DOOR IS USED)FTI-700009$1.4711.47OPWF,144TA LIQUID LEVEL TANK ALARM, 36"144TA-036$315.821315.82OPWF, ENGLISH UNIT TANK GAUGE 200TG AST200TG-ENG$265.611265.61GROUNDING ROD, 2-1/2" LONGFTI-700029$0.8210.82MOUNTING BRKT,ALU,HIGH LEVEL ALARMFTI-313025$31.32131.323" FILL CAP-IRON BODY BRASS CAP178-1000-AC$29.57129.57ANTI-SIPHON VALVE ELEC 115V N.O., 3/4"72228BN5VV00NOH$223.001223FTG 1/2C X 1/2 NPT S/S 90 FTI-222020$22.86122.86FTG 1/2C X 3/4 NPT S/S Str.FTI-230009$16.67116.67HAND PUMP W/HOSE1720A$110.501110.5SPR. RETURN 1" BALL VALVE BRASS71-505-01$123.301123.3VALVE BALL 1" BRASSXV500P-16$15.22115.22LOCKABLE BALL VALVE HANDLE2560-10100$4.1914.19RING PULL STAINLESS STEEL 3/16" x 1 3/16" I.D.FTI-227002$1.7111.71WEDGE ANCHOR, 3/4"X 6-1/4",ZINC PLATEDFTI-114001$2.2549VALVE BALL 1/4" BRS MINIATURE107-701$5.5515.55VALVE BALL 3/4" BRASSXV500P-12$12.97112.97VALVE SWING CHECK 1/2"T-413Y 1/2$24.48124.48VALVE SWING CHECK 3/4"T-413Y 3/4"$29.28129.28SPR. RETURN 3/4" BALL VALVE STAINLESS76-504-01$109.701109.7TIE, 304SS, 5" LONG (MLT1S-CP)FTI-700006$1.0055LEGEND PLATE, PUMP, OFF-ONFTI-700020$7.2517.25DRAIN VENT (1 PER)ECD18$37.67137.67GAUGE STICK 12'FTI-318003$10.29110.2995% VALVE & FLOAT, CFM-2C9582C$420.121420.120000000000002" PIPE0PIPE, STL, SCH40, 2", 21'FTI-240004$2.601436.4PIPE, 2", SCH10, 304, SS, 20'FTI-217002$9.50657FLANGE,2"RF,S/O,BLKFTI-244003$6.361169.96FLANGE,2",150#,RF/SO,304,SSFTI-223001$26.30252.6REDUCER,2"X1 1/2",CONCENFTI-247001$7.3617.36ELBOW,2",90,LR,WELD,BLKFTI-243008$5.56422.24ELBOW,2",SCH10,90LR,SSFTI-222009$8.19432.76TEE,2",SCH40,WELD,BLKFTI-248004$16.77116.77TEE,2",SCH10,WELD,304,SSFTI-228003$15.60115.6REDUCER,2"X1 1/2",SCH10,CONCENFTI-226006$6.51213.02U-BOLT, 2" PIPE, ZINC PLATED WFTI-113003$1.1922.38FLANGE 2" 2" 304SS TTMAFTI-2230046.75854GASBOY PIPING0ELBOW,STREET,1",90,BLKFTI-243002$1.3934.17BUSHING DOUBLE TAP, 2"X1X1184-0400-1B$3.5113.51UNION,1",BLKFTI-249000$3.1726.34PIPE, STL, SCH40, 1", 21'FTI-237000$1.281012.8PIPE, 3/4", 304, SCH40, SSFTI-217001$4.35417.4U-BOLT, 3/4" PIPE, ZINC PLATEFTI-113001$0.7510.75U-BOLT, 1" PIPE, ZINC PLATEDFTI-113002$1.00220GAMMON GAUGE TUBING0FITTING,EL,1/4X1/4COMP,90,SFTI-230014$10.43331.29FITTING,1/4"X1/4",STRAIGHT,SSFTI-230001$5.41316.23TUBE, 1/4", .035 WALL, SS, X 2FTI-219000$2.552051FITTING,3/8"COMPX3/4"NPT STR, S/SFTI-230002$12.65225.3TUBE,3/8", .035WALL,S/S FTI-219001$2.001020 00000000000000REEL, GROUND 25'SA1A1-25 W/O$149.501149.5CABLE, NYLON COVERED 25'GTP-1093$1.6011.6STOP BALLGTP-1486$3.0813.08CABLE STOP BALL CLAMPGTP-1487$5.6015.6CLAMP HEAVY DUTY, AIR FORCE TYPEFTI-500000$10.01110.01BRAIDED GROUNDING STRAP, 8" LONGFTI-700021$2.2912.29CONDUIT BODY 1/2" XLB1$30.4111/2" SEAL-OFF ENY.1$10.4511/2" UNION UNF105 1/2$6.413PULLING ELL 1/2" Y1$10.3921/2" UNION UNF105 1/2$6.4121/2" SEAL-0FF ENY.1$10.451Hub 1/2 H050TB$2.681CONDUIT BODY 1/2" XLB1$30.4113/8" COMPRESSION X 1/2" NPT STRAIGHT BRASS 68C-6-8 18-190$0.9523/4" SEAL-OFF ENY.2$11.7613/4" UNIONS UNF205 3/4$8.992PULLING ELL 3/4" Y2$11.112

[alex108]

This is the spreadsheet I am referring to. You can see all the empty rows throughout. The formula in column e multiplies col c x col d to arrive at a total. I have the formula in col e in each cell down 200 rows. Lets say after I eliminate all the empty rows there are 150 rows left. The formula in rows 150-200 are now gone. The next time I run the sheet any rows below 150 will not have the formula so the calculation is not done unless I go in and copy the formula again to the rows below 150.
I would like to do a macro so that the empty rows are deleted then the calculations are done multiplying rows c x d. untill it comes to an empty row. I hope this helps clear it up.
Thanks for looking it over.
Alex

[lucas]

why not just clear the contents of columns C & D then E will be left alone and on you go..?
Sub Macro1()
Columns("C:D").ClearContents
End Sub

[alex108]

I could clear the contents of c and d but the problem is when I run the macro the next time. Assume I copy various bits of info onto the sheet and they fill various rows between 1 and 200. Because of the structure of what I copy into the test sheet there are blank rows (and not always the same depending on the configuration of the Bill of Material I am constructing).
Now to simplify imagine rows 100 to 150 are blank. I have the sum function in column e1 to e 200. When I clear the blank rows I am left with data in rows 1 to 150. The sum function in col e below row 150 is now gone. The next time I run the configuration any data copied below row 150 will not have the formula in col e so the calculation won't be done.
Thats why I was hoping to clear the blank rows first and then use code to do the calculation. That way it won't matter how many blank rows are cleared since the formula is put back afterwords.

[royUK]

How does clearing Columns C:D delete the formulas in E?

[alex108]

Clearing columns doesn't delete the formulas, it's when I delete the blank rows to consolidate the spreadsheet.

[royUK]

Can you hide the blank rows. Alternatively have a hidden template sheet which contains all the formulas. Use your code to create a copy from that work on it, then when finished with it delete it & move it to another workbook. The template sheet would never be touched.

There's an article on Templates on my website, and an example of copying a sheet in the download Summary.zip

[alex108]

Never thought about hiding the blank rows, that may be the simple ( and therefore most elegant) solution. Can I do a macro that hides them and then when I open the sheet again or run it again unhides them from A1 to E200?
Thanks for taking the time to look at this.

[lucas]

Sub a()
Range("E1").Select
ActiveCell.Formula = "=SUM(C1,D1)"
Selection.AutoFill Destination:=Range("E1:E500"), Type:=xlFillDefault
End Sub

[lucas]

The formula line should be:
ActiveCell.Formula = "=SUM(C1*D1)"
and this could be set up to look for the last line of data in a particular column such as Column A instead of going to row 500

[lucas]

This seems like a poor layout and I don't understand why if your manually consolidating and deleting blank rows that it is such a problem to enter the formula in E1 or E2 and drag it down manually as well....am I missing something?

[alex108]

Wow! Thankyou Lucas, that did the trick. I greatly appreciate the help. Is there a way to only autofill to the last row that has data in it (so I don't get a bunch of "0's" in column "e" ) after I clear the blank rows?

[alex108]

It is not a problem for me to drag the formulas down, but I have to send this out to a few salesman to use to configure a Bill of Material and asking them to do it would be a bit much!

[lucas]

You will have to tell us which column to check to be the last row.....will it be column A?

[alex108]

Column A, B, C, D of the last row will all have data so A will work fine.

[lucas]

Try this Alex....delete some rows and run it.
I didn't take the time to start it on row 2 so you need 0's in C1 & D1

[alex108]

Fantastic- that works like a charm. Thanks for all your help. Nice guitar too!

[lucas]

Excellent Alex,
Note that the guitar is a Gibson....not one of those sissy fenders :devil2:

If you wish get rid of the screen flicker just add application.screenupdating = false to the beginning of the code...just be sure to set it back to true at the end.
Option Explicit
Sub D()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Dim FillRange As Range
Application.ScreenUpdating = False
Range("D1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text <> ""
Range("E1").Select
ActiveCell.Formula = "=SUM(C1*D1)"

Range("D1").Select
Set FillRange = ActiveCell
On Error GoTo 0
If FillRange Is Nothing Then
Exit Sub
End If
Set FillRange = FillRange(1, 2)
lastrow = Cells(65536, ActiveCell.Column).End(xlUp).Row
If lastrow > FillRange.Row Then
FillRange.AutoFill Range(FillRange.Address & ":" & _
Cells(lastrow, FillRange.Column).Address), xlFillDefault
End If
Next
Application.ScreenUpdating = True
End Sub

[mdmackillop]

As I understand it you want to delete the rows with no data in columns A-D. If so try
Sub Rws()
Dim i as long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Application.CountA(Cells(i, 1).Resize(, 4)) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next
Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(, 4) = "=SUM(RC[-2]*RC[-1])"
Application.ScreenUpdating = True
End Sub

[lucas]

Hi Malcolm,
Where were you when I was struggling to get this to work? That's a lot cleaner than fillrange. I'll never be a Wiz at this rate.:doh:

[mdmackillop]

Hi Steve,
The struggle is good for you! You now know how FillRange works, even if it's not required in this case. How do you think I learned this stuff? I still find that there are often better solutions than mine whose code I pinch and recirculate; XLD and Fumei being prime examples.

[lucas]

Oh, I agree completely Malcolm. There is a long list of those I learn from here too. So much talent and so many ways to approach each problem.

[alex108]

Malcom,
I finally had a chance to use your solution. It works great, but it only seems to run for 13 rows? Is it due to a parameter I can change? Where abouts in Scotland are you?

[mdmackillop]

Hi Alex,
I've tested it on your data in Post 3 and it runs fine. It processes up to the last cell in column 1

[lucas]

I got the same result...as Malcolm says it's checking Column A for data to find the last row.

[alex108]

Darn it! sorry for bothering you guys with it again- you are right, I was so focused on col c*d I forgot the sheet I was testing on only had data in col A to row 13.
Must be because I'm working on a long holiday weekend. Thanks again for taking the time to help me solve this.

[mdmackillop]

No problem. It's the simple errors that are often the hardest to spot!