Im looking for code to add new rows and copy the formulas only (not values) into the cells A, B, C, D and F. I have found code that does this in various places online but in every case I have found they duplicate the row then remove the values (so the formulas are left behind). The issue I have however is that it triggers some change event code I have on the sheet which interrupts the process.
Is it not possible to simply insert a new blank row, then copy down the formulas only without values?
you have excel2011, so make a table of your data, so if you add new data the formulas are added immediately.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
formulas utilize values and can refer to other cells (and their values) and render a result unless there is an error triggered . Could you send a sample workbook?
Im trying to avoid tables because I have not had much success with them, and i would like to make my workbook still compatible with older excel of versions.
Is this not possible to do with VB?
I have attached my workbook rcm. On the ReturnData sheet, my plan is for the user to make a cell selection within the sheet records and then allow the user to input the number of rows they wish to add (the code currently requires the user to select cell within a record). It would then add the number of blank rows entered below the active cell with the formulas in cells A, B, C, D and F remaining.
Thanks,
James
perhaps you had bad experience with tables, but the unlimited use of rows in your sheet isn't that good for the speed of your sheet.
Using the max numbers of rows (>1million) is a bad way. Use Defined names like I did with "MyEquipment". Then you can use that in the F-column of "ReturnData"
Thank you bsalv.
However when adding rows in-between existing records, one row adds fine, but if multiple rows are added then the value of E gets copied into all rows except the first.
Other than changing that code for the existing records on ReturnData what else did you do to get the file nearly 200 kb smaller?
in your userform change the value 1 into rI used the defined name "MyEquipment" in the F-column as mentioned in my reaction.
Please Login or Register to view this content.
Do the same in "Search". There you have a lot of unused rows with formulas that look in a huge range. As you work with macros, fill that sheet by a macro and not by formulas.
Thanks, I will look into that.
That line of code however has the same affect. The values still remain in E column (from the second new row added on)
Please Login or Register to view this content.
That fixed the issue of keeping the E cells blank, however it created another issue that it now no longer copies over the formulas to the B, C, D and F cells (Just the A cells only still contain the formula).
Any thoughts on how to fix this bsalv?
see attachment
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks