Microsoft Excel - Delete record with the help of macro

Asked By John on 27-Nov-12 11:58 AM
I want to delete records contains X in the last column or words contains Axiom with the help of macro

S.N.

NAME OF ITEMS

MRP

PACKING SIZE

DISCOUNT (%)

VAT RATES (%)

ORDER QTY
IN
 PCS

ORDER QTY
IN
BOXES

NET PRICE
FOR
PCS

TOTAL
PRICE

COPY

 

Aamlki Rasayan - 100 grm.

65

200

20.63

5

 

 

54.17

 

 

 

Aamvatari Ras - 20 grm.

35

400

20.63

5

 

 

29.17

 

x

 

Aarogya Apple Juice - 1 ltr.

85

12

14.76

5

 

 

76.08

 

 

 

Aarogya Basmati Rice

450

1

11.11

EXEMPTED.

 

 

400.01

 

 

 

Aarogya Dalia (Pushtahar) - 500 grm.

30

30

20.63

5

 

 

25.00

 

x

 

Aarogya Dalia(Wheat) - 500 grm.

18

30

15.34

5

 

 

16.00

 

 

 

Aarogya Vardhani Vati - 20 grm.

40

400

20.63

5

 

 

33.34

 

 

 

Aarogya Vardhani Vati - 40 grm.

80

400

20.63

5

 

 

66.67

 

x

 

Abhayarishta - 450 ml.

60

20

20.63

5

 

 

50.00

 

 

 

Abhrak Bhasm - 5 grm.

13

200

20.63

5

 

 

10.83

 

 

 

Absolute Nasal Drop - 20 ml. (Axiom)

50

 

33.33

5

 

 

35.00

 

 

 

Absolute Ointment - 50 grm. (Axiom)

80

 

33.33

5

 

 

56.00

 

x

 

Adusa Juice - 500 ml. (Axiom)

150

 

33.33

5

 

 

105.01

 

 

Harry Boughen replied to John on 27-Nov-12 03:04 PM
Hello John,

Add the following macro and attach it to a button on your previous search page and call it Data Clear (or whatever takes your fancy).

Sub ClearData()

Dim rngTable As Range
Dim c As Range

Dim intCount, intLoop As Integer

Set rngTable = Range("ListStart").CurrentRegion
intCount = rngTable.Rows.Count
Set rngTable = Range("ListStart")

For intLoop = 1 To intCount
      Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 1) - Range("CatalogStart").Row, -1).EntireRow.Delete
Next intLoop

rngTable.CurrentRegion.ClearContents

End Sub

Then do a search using the FindMatches to search for 'axiom' or whatever other search term you like and click the new button.  If you want your catalog renumbered you will have to run another search or there will have to be more code added to this to do it immediately.

I have not addressed the 'x' in last column question as I was not sure whether you were asking for one or the other.

Regards

Harry
John replied to Harry Boughen on 27-Nov-12 09:28 PM
Sir, I want to do this on a new sheet and I don't want to delete entire records. I just want to delete records contanins Axiom or not contains Axiom or X mark in the last coloum.

Thank and regards.
Harry Boughen replied to John on 27-Nov-12 11:12 PM
Hello John,

The attached file has a new version of the previous file that will search for words in your product list and for the presence of an "x" in ColumnK on a separate page (Catalog_1).  The delete button will delete the selected records.

xmarker.zip

Regards

Harry