Excel 2003 VBA coding tweak

[maglerem]

Hello
I want to thank any and everyone in advance for helping me on this! I am using excel 2003 and am a bit of a novice when it comes to VBA coding.

I found a code that works almost perfectly for me there are only 2 problems –

1.I only want it to apply to ONLY the sheet I am in NOT all the sheets in the workbook

2.I want it to erase ALL rows that contain formulas that come back with a #N/A in the “G” column.

This code seems to miss a few of the #N/A s. Here is the code –

Sub test()
For Each Sheet In ActiveWorkbook.Sheets
For Each cell In Sheet.Columns("J:K").Cells
If IsError(cell.Value) = True Then
Sheet.Rows(cell.Row).Delete (xlUp)
End If
Next cell

Next Sheet
End Sub

Here is the link I found the code at - "sqldrill.com/excel/programming-vba-vb-c-etc/356944-delete-row-if-formula-brings-back-error.html"

Thank you thank you thank you!
Erica

[stanleydgrom]

maglerem,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


[vba]

Option Explicit
Sub test()
' stanleydgrom, 02/03/2011
' http://www.vbaexpress.com/forum/showthread.php?t=35989
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With ActiveSheet
LR = .Cells(Rows.Count, "G").End(xlUp).Row
For a = LR To 1 Step -1
If WorksheetFunction.IsNA(.Range("G" & a)) Then .Rows(a).Delete
Next a
End With
Application.ScreenUpdating = True
End Sub

[/vba]


Then run the test macro.


Have a great day,
Stan