Delete rows with defined text from all months thru VBA

[PRADEEPB270]

Actually,this thread was started by 'RENU1973' as on 21.01.2014.It was "Insert rows with defined text for all months thru VBA".Very nice post. Very nice post as well as very nice solution posted by Hans .Appreciated.

It will be also very useful for me if some changes made as my subject.
Please refer my attach file,as I want to exactly what,whenever typing in cell no.'A2' ,then,all rows should be delete.

For an example,I have typed 'THAPA' in attach file in cell no.A2.Now,it should be deleted as the same process if I typed in cell No.A1 for insert.

Hans,Can you make some changes in attach macro file?

Thanks in well advance.

[Rudi]

I understand the request to delete all rows in the activesheet based on the text in cell A2 ("THAPA"), but I do not understand what you mean by "the same process if I typed in cell No.A1 for insert."

Must cell A2 have rows deleted based on its value
And cell A1 have rows inserted based on its value (if so, where must GYAN rows be inserted, and what about the date and the values?)

Please explain.
TX

[PRADEEPB270]

A1 cell mean 'Insert' and A2 cell mean delete in my attach file.If I want to insert 'Flag' D then I will type in A1 cell and delete the same after inserting will type in A2.

[Rudi]

This code should remove the records that contain the name in cell A2:

Code: Select all

Sub UpdateData()
Dim rData As Range
Dim sDel As String
Dim sIns As String
Dim i As Integer
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    sDel = Range("A2").Value
    sIns = Range("A1").Value
    Set rData = Range("A3", Range("A" & Rows.Count).End(xlUp))
    For i = rData.Rows.Count To 2 Step -1
        If rData.Cells(i).Offset(0, 1).Value = sDel Then rData.Cells(i).EntireRow.Delete
    Next i
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

As for the inserting part, I am unsure how the value "GYAN" must be inserted.
- Where must records for GYAN be added, how many times must it be added, etc?? (I must be missing a point here.)

[PRADEEPB270]

Macro is working perfect.Thanks Rudi for nice help.
Have a nice day.

[Rudi]

What about the insert part?

[PRADEEPB270]

It is working well as earlier.There is no change in insert part.Both macros ( Insert and Delete ) are working well.No problem.

[Rudi]

Good to know. TX for the feedback.