So here is the set up of my worksheet
Name of Stock Long Inventory Short Inventory Apple 0 50 Apple 30 0 Google 10 0 Google 40 0 Google 20 0 . . .so essentially i have blocks of stocks of the same name (block sizes vary), and although MOST of them are EITHER long only OR short only, BUT some of them are BOTH long and short (ie look at apple above, one apply entry as 0 long and 50 short, while the next is like how most are (ie either long or short only)
How do I create a macro to identify the names that are BOTH long AND short...? what's making it hard is that although the same names are together, the size of the blocks of the same name is variable (ie there are 6 GS together, 11 citigroups etc, so i cant make a general formula to scan thru the blocks of holdings)
Please help anyone who knows VBA...any way to identify names that are BOTH long and short would help i.e if u can even highlight them or better yet copy and paste them to a new worksheet...thanks! .
Here's a first stab at it. Basically, just copy the entire block of values into a new sheet. Make sure that "Name of Stock" is in the top left cell of the new sheet. Then select the range which you just pasted into the new sheet. Disclaimer: I don't have Excel on my computer, so I haven't tried to run this macro...
sub deleteUnwanted() dim rangeToModify, rangeToDelete as range dim i as integer set rangeToModify = selection for i = 1 to rangeToModify.rows.count set rangeToDelete = range(activesheet.cells(1+i,1),activesheet.cells(1+i,256)) if rangeToDelete.cells(1,2) = 0 OR rangeToDelete.cells(1,3) = 0 Then rangeToDelete.delete end if next i end sub
Thanks a lot from your help. I ran your macro, but I'm not sure what it is deleting.. it delets stocks that are both long and short as well... and what should be the next step??
Make it easier...
=IF(AND(B2>0,C2>0),"keep","delete")
This should work - it addresses the issue of numerous entries. Please note it will stop on the 1st blank cell in Column A.
Sub DeleteNotLS() Dim Loc As Double Dim Keepers As String Dim Trash As String For c = 1 To 65536 If InStr(Keepers, Range("A" & c)) = 0 And InStr(Trash, Range("A" & c)) = 0 Then If Application.WorksheetFunction.SumIf(Range("A:A"), "=" & Range("A" & c), Range("B:B")) > 0 And Application.WorksheetFunction.SumIf(Range("A:A"), "=" & Range("A" & c), Range("C:C")) > 0 Then Keepers = Keepers & Range("A" & c) & "11111" Else Trash = Trash & Range("A" & c) & "11111" End If End If If Range("A" & c) = "" Then Exit For Next c Loc = 1 For c = 1 To 65536 If InStr(Keepers, Range("A" & Loc)) = 0 Then Rows(Loc).Delete Else Loc = Loc + 1 End If If Range("A" & Loc) = "" Then Exit For Next c End Sub
Hope that helps.
Omnis at aut sed quia cupiditate in assumenda. Provident sed quaerat sit excepturi aut delectus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...