PDA

View Full Version : Auto add row and copy formula



kumaramitouj
10-26-2010, 01:55 AM
Hi,

I am using the following code to delete all rows with "done" data.

what i want is it should delete the same from all sub sheets aswell and insert a row in all sheets including the master, the row thus added should auto copy the fomula from the above row....

is it possible?

Private Sub Remove_Click()
Range("g2").Activate
Do While ActiveCell.Row <= Cells(Rows.Count, 1).End(xlUp).Row
reCheck:
If ActiveCell.Value = "Done" Then
ActiveCell.EntireRow.Delete
GoTo reCheck
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

lynnnow
10-26-2010, 03:22 AM
Why not just clear the appropriate cells of their values, keeping the formula intact?

kumaramitouj
10-26-2010, 04:11 AM
Lynnnow hi,

can you do that in a macro? as i am not well versed with the same.can you please help me with the code The need for a macro is:
1. as the same is quicker rather selecting and deleting each row.
2. if we selcet and delet the values there would be empty rows in between.
can you please help.....

lynnnow
10-26-2010, 05:06 AM
Looking at your file, which columns would you like to be cleared?

For example you could use:

If ActiveCell.Value = "Done" Then
Range(Cells(ActiveCell.Row, 2), Cells(activecell.row, 6)).ClearContents
Cells(ActiveCell.Row, 8).ClearContents
GoTo reCheck

This will leave the Status column untouched and clear the values from the columns before the Status column and one column after the Status column.

Alternatively, if you want the contents of the entire row to be cleared, just use

Range(Cells(ActiveCell.Row, 2), Cells(activecell.row, 8)).ClearContents

HTH