Excel Clear Contents of Row

[Guest_imported]

Here's a good question :

I've got 8 columns, 3 of which are formatted formula columns (i.e. G2=E2*F2 and G2 is in green, bold font). I also have a list of about 50 rows. The rest of the rows on the sheet are hidden. Is it possible to somehow delete a row without clicking the row header and selecting delete row?

For example: I want to be able to clear out the contents of the 5 non-formula cells in row 8, and have the other rows below it shift their 5 non-formula cells up. If I were to delete the whole row, a new row would appear at the bottom of my sheet and would not have the 3 formula columns, they would be blank. I need to preserve all 50 rows of those formatted forumla columns.

Any suggestions would be very helpul!

 

[SkipVought]

Hi, ericire,

1. You need to join Tek-Tips (no charge) and become a contributing member

2. What you described doing, (Shifting rows up) would destroy your data integrity. What you might do is...

A. Delete the row entirely. This shifts eferyting up

B. Insert a blank, formatted row at the bottom to preserve you 50-row requirement.

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

SkipVought, I realize that would be the easiest thing to do, but the person I am giving this sheet to is very non-computer-literate, and doesn't even know how to format rows. They would be the ones "deleting" rows, and would not know how to format a new, blank row to match the others.

The way I have it set up now, all 50 rows have a certain font format and color scheme for individual cells in that row. When I delete a row, the blank row inserted has no format (the font is different, as well as the color and style). I preformatted the 50 rows (even though only 35 are taken now) to allow for adding/deleting rows. But I don't want to delete rows, just clear them and bring the data below them up, so that all 50 rows (including the last row) will stay formatted properly.

It's not a one time thing, it will happen often. The rows represent associates, and if the associate quits or gets fired, they must be cleared without leaving a gap in the table.

Hopes this makes sense,
ericire

PS - I will join soon.

 

[SkipVought]

ericire ,

You are looking for an easy spreadsheet solution, when, in fact, creating a spreadsheet application and distributing it to users without 'bullet-proofing' it is asking for trouble.

Have you protected the sheet so that the formulae cannot be deleted? This will take a VBA solution more than likely. Would you be able to do this...

1. Unlock all cells in the columns that the user can change - lock all others. You do this one time.

2. Protect the worksheet for user update

3. whenever the user deletes a row (you can detect using the Worksheet_Change event), you sort the data by the appropriate columns and the empty row migrates to the bottom - then you protect the sheet again.

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

SkipVought, I'm not quite sure what you mean... I am very much familiar with protecting the worksheet before distribution, and it is what I planned on doing. However, I don't see how this will help me because I will not be able to modify the workbook after I give it to the person I am creating it for. I will never see it again.

I am also not sure what you mean by protecting the sheet for "user update," I only have options to protect for "Contents, Objects, and Scenarios." Maybe you are refering to a different version?


Now, A challenging Question for Anyone that Can Help...

I do not want a new blank row inserted. I do not want to actually delete a row. I just want to clear a row, and move the rows beneath it up.

Perhaps I should give a simple example of what my sheet is like:

Assosciate Budget Current % to Goal
John $4,000 $301 7.5%

Sally $4,000 $595 14.9%

Thomas $3,400 $7,233 212.7%


In this example:
The green column (% to Goal) is a formula. It is also a formatted column (certain font, style, color, etc.).
To allow for more employees, I have made a total of 10 rows and hidden the rest of the rows. In each of the 10 rows the "% to goal%" column is preformatted with the specific font and format. I used an IF formula in this "% to goal" column so that it would show a value only if there is content in the first column of that row (the name cell). Therefore, adding a forth employee (Joe for example) in row 4, as well as his budget and current values, will automatically produce a formatted value in the "%to goal" cell of his row.

Now, this is my dilemma:
If Sally quits, I want to be able to put my mouse over her "Name, Budget, and Current" cells and click clear. If I delete the entire row, a blank row (that doesn't have the "% to goal" cell preformatted) will be inserted at the bottom of the sheet and now I can only have a total of 9 employees in the table that have a nicely formatted row without going in and reformatting this new last row. I.E., If eventually there are 10 employees, the last one will not have a Green, Bold, Formulated "% to goal" column. This will only worsen with the more deleted employees. At such a rate, eventually none of the preformatted rows will be left.

Finally, my restated question:
Can I make it so that when Sally's name is cleared, Thomas's row is moved up to replace Sally? Or better yet, Can I make it so that if I were to delete an entire row, the new row that is automatically inserted on the bottom of the sheet would have the same format as the one above it (color, font, style, formula, etc.)?

 

[SkipVought]

Are you ready to write some VBA?

AGAIN...

what you are wanting to do cannot be done with spreadsheet functions. It takes programming, using Visual BASIC for Applications (VBA). I outlined a method above. VBA is built into Excel. Try recording some of what you want to do in macros and then come back an we can talk about putting it all together. Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thank you SkipVought for you patience and openmindedness... but I think since obviously it is an impossible goal and since I don't know how to just "do some of what I want to do in macros" , I will forget about my question and move on to other methods.

 

[NSoulBri]

Unless I'm missing the point the remedy here is simple.

Format the entire sheet first and copy your '% to goal' formula all the way down. Bri

"Soul music is for life, not just christmas"

 

[SkipVought]

Menu Item - Tools/Macros/Record New Macro

This is a powerful tool. You can record various actions and then see what kind of VBA code does what you want to do. alt+F11 gets you to the VB Editor after you have recorded a macro.

Are you up to the task? It will take you learning some things about VBA. You begin by "playing around" with the record function and playing back - looking at Help and asking questions. Skip,
metzgsk@voughtaircraft.com