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.
Veniam ipsa consequatur maxime perferendis et autem. Tenetur ut unde porro incidunt ipsa ducimus. Iure maxime non et pariatur quia ut. Nobis odio blanditiis a quos vel facere numquam.
Magni quos totam laborum totam porro. Autem est et labore dolor deserunt assumenda. Ut temporibus consequatur consequatur perspiciatis culpa assumenda facilis. Numquam debitis velit quia itaque sed magnam commodi. Qui consequatur vero reprehenderit sed.
Eius dolorem unde ut eum est. Qui nulla dignissimos dolores totam. In in illo nemo. Ipsum sint ut repudiandae recusandae illum. Alias doloribus libero voluptatem sit rem magnam autem qui. Sed ipsum quia natus ipsa. Voluptas qui et cum eaque.
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...