![\"ExcelList\"](\"http:\/\/miguelontheweb.com\/wordpress\/wp-content\/uploads\/2015\/11\/ExcelList1.png\")
<\/a><\/p>\nYou can tell by looking at this list that you can easily delete the positive 20 and the -20 \u00a0without changing the total sum of the list. In addition, we can also remove the -10 and 10. \u00a0However, what if the list was a list of 100 rows and you would like to continuously remove items without changing the totals. Well that’s where VBA would come in. The total of this list is 100 as you can tell by looking at cell B8<\/strong>. I will go into the\u00a0developer tab in Excel and click on Visual Basic. I \u00a0will then find “Sheet1” in the VBA project window usually on the left hand side. Double click on Sheet1(Sheet1) to open the editor on the right hand side. From\u00a0the drop down menus on the top right hand side , select “Worksheet” and “SelectionChange” respectively. \u00a0Finally, add code to the “SelectionChange<\/strong>” event of Sheet1 so that it fires this event every time we change something on this sheet.<\/p>\n We can quickly step through the code, there is not much to it. First we \u00a0cast \u00a0the CellValue \u00a0variable as a string. Than we are assigning the value from cell B8<\/strong> to the variable CellValue. Finally we are testing the CellValue variable to see if the amount is not equal to the amount we don’t want to change, in this case 100. Thats all there is to it.<\/p>\n Pretty simple, but helpful,\u00a0\u00a0leave me some comments below. I would love to hear what yout thoughts are on this post.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" There are times while working with an excel list that you would like to delete items of a\u00a0list without changing the total Sum of that list. Example of this situation often occur when you are reconciling an account and you have various positive and negative values that offset each other. \u00a0In this small article I … <\/p>\nprivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\n\t\r\n\tDim CellValue As String\r\n\t\r\n\tCellValue = Range(\"B8\").Value\r\n\tIf CellValue <> \"100\" Then\r\n\t\tMsgBox (\"OOPS GO BACK\")\r\n\t\t\r\n\tEnd If\r\nEnd Sub\r\n<\/pre>\n