Excel - Formula Based Row Deletion

April 21, 2009 at 10:23:55
Specs: Windows XP/Excel2003
I have a, Excel workbook in which there are 2 spreadsheets; sheet1 contains a large amount of employee data and sheet2 contains names & project teams.

I currently use formula '=VLOOKUP(H4, Sheet2!A:B, 2, FALSE)' on Sheet1(columnA) to map project teams (from Sheet2, columnB) based by employee names from Sheet1, columnH.

I need to go 1 step further and delete any rows where a project team was not returned (now displaying #N/A) on sheet1.

I've tried to modify other provided examples, but with no success - just a bunch of errors.

Can anyone help?


See More: Excel - Formula Based Row Deletion

Report •

April 21, 2009 at 10:53:00
re: Your subject line Formula Based Row Deletion

I assume you are aware that you can't actually delete a Row via a "formula". You can however do it with VBA.

re: I've tried to modify other provided examples

What examples have you tried?

Report •

April 21, 2009 at 12:32:04
I do know that, but I'm new to VB.

Basically I'm trying to delete any rows that did not have a project team returned into ColumnA as a result of the VLOOKUP function.

Here's the one I tried using from online and tweaking it; my issue is that I use vlookup to get the project team into sheet1 (column A)based on a table with the individual's name & project team from sheet2.

In using the sample below, it expects actual data and not formulas in the cell to determine the criteria to delete.

Sub DeleteRows()
Dim strToDelete As String
Dim rngSrc As Range
Dim NumRows As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ThisCol As Integer
Dim J As Integer
Dim DeletedRows As Integer

strToDelete = InputBox("Value to Trigger Delete?", "Delete Rows")
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
ThisCol = rngSrc.Column

For J = ThatRow To ThisRow Step -1
If Cells(J, ThisCol) = strToDelete Then
Selection.Delete Shift:=xlUp
DeletedRows = DeletedRows + 1
End If
Next J
MsgBox "Number of deleted rows: " & DeletedRows
End Sub

Any suggestions? Or could you point me in the right direction? (I'd like to understand so I can learn from this - I'm new to coding...)

Report •

April 21, 2009 at 13:15:28
If I wanted to delete rows that contained #N/A in Column A, and I wasn't concerned about any other error showing up in that cell, I'd use something like this:

Sub Del_NA()
'Find last row with data in Column A
lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Start at bottom and delete rows with errors
 For myNA = lastRow To 1 Step -1
  If IsError(Cells(myNA, 1)) Then _
   Cells(myNA, 1).EntireRow.Delete
End Sub

Report •

Related Solutions

April 21, 2009 at 13:57:51
Huh -simple...I like it!

I'm going to run it and I'll let you know how it goes. Thanks for your help!!

Report •

April 21, 2009 at 19:44:17
Of course, you are going to run it on backup copy of your file, right?

Report •

April 23, 2009 at 06:17:09
Of course - I run all of my testing on a local copy!!

Thanks for your help - it worked perfectly!

Report •

Ask Question