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
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.