Excel: delete entire row when other cell equals a defined value

2
votes

I have seen (searched) similar examples, but not quite what I am looking for.

I have a Workbook in Excel that has several sheets, Sheet A and B. These sheets have a bunch of data, so in order to display the most significant data on Sheet B from Sheet A, I want to mirror only the rows that I want to specify depending on the cell values on SheetA....I need to delete entire rows in Sheet B depending on the value in Sheet A.

For instance, in Sheet A I have column X with 10 values (Yes/No), and I have linked the same data with formulas back to Sheet B. That is, that if in SheetA X1="Yes", then SheetB cell Y1="Done"...if SheetA X2="Yes", then SheetB cell Y2="Done"...if SheetA X3="No", then SheetB cell Y1="Missing"..and so on.

So I only want the rows in SheetB with cell values="Done" to be there and thus want rows with cell values="Missing" to be automatically deleted. In this fashion, I would be creating a table that only includes the rows with "Done" values for the specified cell.

I know there are macros in Excel, but I have never written code in VBA, and the language handlers and variables escapes me entirely.

Is there a way to write a macro that can be called with in a formula; that is, e.x) if(A10="Yes", "", delete row macro here)???

Thanks!

2
excelexcel-formulaexcel-2010vba
Recording macros is always a good place to start, that will show you how to delete a row in vba. From there, you can invoke it how you wish, in this example perhaps a simple if statement would suffice. – redditor

2 Answers

1
votes

From the wording in your question it seems you want to create a function that can be used in a cell that will alter other cells. That cannot be done. The functions, when used in a formula, are limited to changing the cell itself, and not other cells.

0
votes

More then one way to skin a cat. Like Abe said you can`t use formula to alter other cells. But you can use VBA. The below sub removes entire rows where the cell in range is equal to 1. But you can make it equal to whatever you want.

Sub DeleteRows()
Dim FoundCell As Range
Set FoundCell = Worksheets("SheetB").Range("YourRange").Find(what:=1)
Do Until FoundCell Is Nothing
    FoundCell.EntireRow.Delete
    Set FoundCell = Worksheets("SheetB").Range("YourRange").FindNext
Loop
End Sub

Of course this is extra work. What you should do instead of copying the data from A to B and then processing it, just copy the done cells from A to B.