Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 19th, 2016, 09:04 PM
Registered User
Join Date: Nov 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA code to search for a range and delete other cells


I have an Excel sheet with students that have failed courses, and I need to remove the class(es) and mark(s) that are passing (mark of 50-100). Here is how my Excel sheet is set up:

A1 B1 C1 D1 E1

Name Class #1 Mark in class #1 Class #2 Mark in class #2

... it goes on from there for the next 3 courses and their respective marks.

Let's say for example that John is failing 2 classes (mark of 49 or less). I want to be able to remove the other 3 classes he is passing (along with the grade for that class) and just leave the failing class's names and respective failing marks.

Ideally, I would also like to be able to remove the entire row for any students who are not failing anything, but I can live without it.

If anyone has any thoughts, I'd greatly appreciate it.

Thanks very much in advance!
Old November 20th, 2016, 06:08 AM
Authorized User
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts


Once you find what you are looking for then call the entireRow object to have the entire row, chain the delete function. It returns the entire row of current cell.
Alternative to entireRow, if there's other non-related data in other columns, then use: set range2delete = range(cells(row,1 or whatever is start column),555(row,5 or whatever is end column ))
Row in the above returns the current row.

Apologies for spelling errors, using a mobile phone.
Nostalgia 4 Infinity

Similar Threads
Thread Thread Starter Forum Replies Last Post
VbA change colour cells based on cells values in other sheets Bruno Excel VBA 3 January 26th, 2016 05:52 PM
VbA change colour cells based on cells values in oother sheets Bruno Excel VBA 1 January 26th, 2016 08:14 AM
Background color in a range of cells billy1r Excel VBA 9 February 23rd, 2009 11:45 PM
Search/Update in multiple worksheets VBA code help utalwalk Excel VBA 1 April 19th, 2006 04:42 AM
Searching for Duplicates in A range of Cells smartgir Excel VBA 1 October 23rd, 2003 01:44 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.