Delete Row Automatically

[abdullahsikandar]

Hi Guys,

I need a small help from you guys, I have a excel file where i am importing data from different files. What i need is to delete the whole row when the user write "Done" on the last column called 'AT'.

Like this:

Name ID Status

Abdul 1 Done
Rag 2 Done
Roh 3 Undone
Zai 4 Done

So the result would be like this

Roh 3 Undone.

When we run the macro.

Kindly help me in this matter.

 

[VoG]

Cross posted http://www.excelforum.com/excel-programming-vba-macros/1005722-delete-whole-row.htm

Please read Excelguru Help Site - A message to forum cross posters

 

[abdullahsikandar]

Sorry and thanks for telling me as i am new user on Forum so i am not aware of this.

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteDoneInColumnAT()
  Columns("AT").Replace "Done", "#N/A", xlWhole, , False
  Columns("AT").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[abdullahsikandar]

Hi,

Thanks for your reply.. but still its not working. We already have a "Save" button and what i need when we press that "Save" button it will automatically detect if column 'AT' = "Done" in any column in 'AT', so it will delete that whole row.

Thanks

 

[Rick Rothstein]

Hi,

Thanks for your reply.. but still its not working. We already have a "Save" button and what i need when we press that "Save" button it will automatically detect if column 'AT' = "Done" in any column in 'AT', so it will delete that whole row.

So, take the two lines of code from the macro I posted and insert them into the code you are running from your "Save" button at the point in your code when you want to do the deletions. Note, though, that my code does not use a loop... it processes all of Column AT at once... so do not put those two lines of code inside any loops you may have.

 

[abdullahsikandar]

Hi,

Thank you so much its working ... Can you help me little bit more .. If the user write it "Undone" so its fine but if its empty or null so it will pop up the message that kindly fill that 'AT' column first.

Thanks

 

[abdullahsikandar]

one more thing if User wrote "Undone" so it will not do anything and if the user remains it null so it will pop up with the message.

Thanks

 

[Rick Rothstein]

one more thing if User wrote "Undone" so it will not do anything and if the user remains it null so it will pop up with the message.

Something like this maybe...

If WorksheetFunction.CountIf(Range("AT1", Cells(Rows.Count, "AT").End(xlUp)), "") Then
  MsgBox "You have blank cells in Column AT... please fill them in"
  Exit Sub
End If

I am not sure about the "Exit Sub" command because I do not know how your "Save" button code is structured, but I am guessing it is what you would want to do. Based on my best guess at what your "Save" button code is probably doing, I am guessing you will want to install this code at the beginning of your existing code.

 

[abdullahsikandar]

Thanks for your reply Rick its working for me now .