removing multiple rows based on criteria
How to remove multiple rows with the name "DPL" in the string in column Resource and HLR as string also in Resource Column. Another words can have DPL live as string but will still remove because it found DPL. Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Silly question - how do I add the code to my spreadsheet?
ASKER
Figured it out - thanks
In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)
Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.
Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)
Press Alt+F11 again to go back to Excel
Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’, hold down the Shift key and type the letter 'D' (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+D. The macro can also be added to a button. If you want to do that and you don't know how, let me know.
When you close the workbook you will need to save it as an xlsm, xlsb or xls file if it’s not already one of those.
Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.
Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)
Press Alt+F11 again to go back to Excel
Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’, hold down the Shift key and type the letter 'D' (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+D. The macro can also be added to a button. If you want to do that and you don't know how, let me know.
When you close the workbook you will need to save it as an xlsm, xlsb or xls file if it’s not already one of those.
ASKER
You are the bomb. Thanks will implement and test
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
Experts Exchange Top Expert VBA 2018
Experts Exchange Distinguished Expert in Excel 2018
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
Experts Exchange Top Expert VBA 2018
Experts Exchange Distinguished Expert in Excel 2018
ASKER
does the resource_col change ie
RESOURCE_COL & "1048576" based on the change from RESOURCE_COL = "A" to RESOURCE_COL = "D" Thanks in advance
RESOURCE_COL & "1048576" based on the change from RESOURCE_COL = "A" to RESOURCE_COL = "D" Thanks in advance
Yes. I could also modify the code to "hard code" column 'D' (or whatever the correct column is) so that you don't need to worry about it.
No joy with using autofilter then?
Select your data area and apply the AutoFilter using the large funnel icon on the Data menu. This will then add a drop-down box next to all of the column headers.
Using the drop-down next to Resource header you can enter DPL as a filter value, click OK and it will filter the list to show only values containing the string. Click the drop-down again and enter filter value of HLR but this time rather than clicking OK immediatley, in the list of values below the input box there will be an option to "Add to Existing filter", check that option and then click OK. The data will now be filtered on values containing either of the two strings. You can now select the data as a block and delete the rows, hidden rows will not be affected.
Remove the Autofilter by clicking the funnel icon or unhide the remaining rows using the Show all option next to it.