deleting rows containing excel vba

Question

New to VBA and this is my first time posting a question. I have a workbook that contains about 50k records. The data is comprised of approx. 2700 us stocks with with bi-weekly data updates to look at price, volume traded, etc. I will be updating the data bi-weekly, and when I do I am going to eliminate securities which don't meet certain criteria. At each update, there may be 30 - 50 securities which need to be removed, so it is time consuming to filter for each one and delete all rows for that security.

My hope is to be able to maintain a table on a sheet called "Filters" that I can manually populate with my "deletes" each time I need to remove certain securities, then run a macro that filters down to just those securities and deletes all rows.

Here is what I've tried so far just to filter the data down to the required rows. I can't seem to get it to work. I get an error in the bold line below.

I have a table called "Filtering" named in Sheet "Filters" (fifth sheet) and the raw data is in a sheet called "Data" (first sheet). Any help is greatly appreciated.

Sub deleterows()

Dim Filtering As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim sedol As Range, rngData As Range


Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Filters")

Set rngData = ws1.Range("$D$1").CurrentRegion
**Set sedol = ws2.Range("Filtering")**

Filtering = sedol.Value

rngData.AutoFilter Field:=1, _
                  Criteria1:=Application.Transpose(Filtering), _
                  Operator:=xlFilterValues

How did you name your table? Did you do it using **[this method](http://office.microsoft.com/en-001/excel-help/rename-an-excel-table-HA010223129.aspx)**? — bp_, Nov 03 '14 at 16:12
These kind of questions where deleting is done using Autofilter has been covered so many times. Did you not do a search in SO? :) — Siddharth Rout, Nov 03 '14 at 16:27
That was the issue, I think my table was not named properly. I did it through the name manager via a formula. I went back and created a table and it added the header name as recommended by @jeeped and it worked great. I did search this in SO as well, which is how I was able to create the base code that I posted. I just couldn't figure out why I kept getting 1004 error, so I posted the question. Thanks a lot for the help. — KPC, Nov 03 '14 at 16:47

David Zemens · Answer 1 · 2014-11-03T16:23:00.403

0

You may be able to adapt something like this. I assume that the range "Filtering" is a single-column range which contains the item to be deleted.

Dim r as Integer

Set sedol = ws2.Range("Filtering")

For r = 1 to sedo1.Rows.Count

    Filtering = sedol(r).Value

    rngData.AutoFilter Field:=1, _
                  Criteria1:=Filtering, _
                  Operator:=xlFilterValues

    rngData.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Next

Above is for a Name range object. If instead you have a table ListObject, try changing the assignment to sedo1, like so:

Set sedo1 = ws2.ListObjects("Filtering").DataBodyRange

edited Nov 03 '14 at 16:23

answered Nov 03 '14 at 16:07

David Zemens

53,033
11
81
130

Thank you for the response. I tried this code but am still getting the same error when I get to the line: "Set sedol = ws2.Range("Filtering"). The error is 'Run-Time error '1004': Method 'Range' of object '_Worksheet' failed. – KPC Nov 03 '14 at 16:19
That would suggest that you do **not** have such a named range that exists on `ws2`. Double-check your Named range exists :) – David Zemens Nov 03 '14 at 16:20
Alternatively, it looks like you may have a table (which is a listobject), so you could try this: `Set sedo1 = ws2.ListObjects("Filtering").DataBodyRange` – David Zemens Nov 03 '14 at 16:22

score 0 · Accepted Answer · answered Nov 03 '14 at 16:20

I set up a quick test workbook and got this working immediately by changing that one line to:

Set sedol = ws2.Range("Filtering[Stock]")

The table I created (Insert, Table then Table Tools, Table Name Filtering) on the Filters worksheet had a header row with the column label being Stock. I'm unclear on the exact nature of the table you created but this worked for me. I believe you just have to change the way you are referencing the column of data within your table. If you cannot get it working quickly, generate a macro that records going to the column of stocks and then tapping Ctrl+<spacebar> to select the column of stock names within the table. That might clear up any syntax issues on selecting the column of data from the table.

deleting rows containing excel vba

2 Answers2