Deleting Macro

[robertjtucker]

Ok here is one, I am trying to create a macro ( one button) that will when pushed delete certain cells in a row.
What I am wanting to do is, if I select row 3, it will delete cells a,c,d,e,g becuase I have formulas in b,f,h,i. I don't want them deleted I just need the other cells information deleted and the cells with formulas will clear automatically. But it has to work by selection, if I click 3 it will only work in 3, or if I click 7 if will only work in 7.
Any Ideas? I gave up after a day, didn't have the time to worry about it anymore, so I purpose it to you all.
Thanks for the help,
Bob

[Leith Ross]

Hello Robert,

This macro will check the row based on the active cell and then delete the appropriate cells. Paste the code into your command button. This example uses CommandButton1.


Private Sub CommandButton1_Click()

Dim R As Long

R = Excel.ActiveCell.Row
With Excel.ActiveSheet
.Cells(R, "A").Value = ""
.Cells(R, "C").Value = ""
.Cells(R, "D").Value = ""
.Cells(R, "E").Value = ""
.Cells(R, "G").Value = ""
End With

End Sub

If you want to restrict this to a specific worksheet change With Excel.ActiveCell to With Excel.Worksheets("<worksheet name>").

Hope you find this useful,
Leith Ross

[robertjtucker]

Thanks That works really well, I have to change some of my sorts, but that is ok. Unless there is a way to delete instead of assigning "" as a value. Is it possible to make so I can remove multiple rows of information instead of one at a time?
Thanks again for the help.
Bob

[Leith Ross]

Hello Bob,

Yes you can clear out multiple rows. I will have to rewrite the macro to do that. With regard to deleting the cells, I am guessing that a Empty or Blank cell causes your sorting to stop a that cell. The cells can be deleted, that is shifted up, to prevent a sorting problem. I'll work on a rewrite today for you.

Thanks,
Leith Ross