Hey guys,
I looked at some other questions similar to this one, but I have two things I'm trying to accomplish so I just went ahead and posted a thread. In the attached screenshot, I am trying to do two things:
1) Column E was a calculated by running a vlookup of column C on another sheet. The values in column E will be either #N/A or the value of its corresponding cell in column C. I should note that they have been copied and pasted so that the actual values are in the column and not the fromula used to obtain them. What I am trying to do is basically delete any row where the value is not #N/A (IOW anywhere where columns C and E have the same value). I just recently copied and pasted the values as mentioned above as I thought maybe it was having issues because the formula was in there (not sure if that step is needed or not though).
2) Column Z has values of 0, 1, 2, 3, 4, 7, H, C, or it is blank. Those are the only values it will have. With that column, I am trying to delete any row where the value is 2, 3, 4, 7, H, or C. I have tried the following code, and it runs but for some reason doesn't touch row 2 (also takes a long time to run)
\CODE
Sub Removing_Xpress()
'
' Removing_Xpress Macro
Dim i As Long, j As Long, rw As Long
Dim rng As Range, cel As Range
Dim arrWords
Dim xlCalc As XlCalculation
arrWords = Array(2, 3, 4, 7, "H", "C")
xlCalc = Application.Calculation
Set rng = Range("Z2:Z40000")
For rw = rng.Rows(rng.Rows.Count).Row To rng.Rows(1).Row Step -1
For j = 0 To UBound(arrWords)
If InStr(1, rng(rw, 1), arrWords(j), vbTextCompare) Then
bDel = True
rng.Parent.Rows(rw).EntireRow.Delete
Exit For
End If
Next
Next
Application.Calculation = xlCalc
End Sub
\CODE
There might very well be a better way to accomplish this, but I haven't done a lot of VBA work in a long time and this code came after lots of online research.
Any help you could provide would be appreciated. I'm sure this is a rather simple solution and I am overthinking it. Thanks everyone
Bookmarks