Delete #N/A rows Formula
I tried to delete rows in a MACRO that are returned as #N/A after
performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have to delete #N/A rows is not working & I was hoping someone has any suggestions. In my VLOOKUP the data is pasted as values, by doing so is the #N/A not recognized as an error? Should I combine the two MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows: VLOOKUP Worksheets("Group 40").Activate Dim rng As Range With Worksheets("Group 40") Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With rng.Offset(0, 1).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,1,FALSE)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value rng.Offset(0, 2).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,2,FALSE)" rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value End Sub DELETE #N/A Rows On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _ .EntireRow.Delete On Error GoTo 0 End Sub Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
Don't you like the answer I give you in your other thread Steve???
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "STEVEB" wrote in message ... I tried to delete rows in a MACRO that are returned as #N/A after performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have to delete #N/A rows is not working & I was hoping someone has any suggestions. In my VLOOKUP the data is pasted as values, by doing so is the #N/A not recognized as an error? Should I combine the two MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows: VLOOKUP Worksheets("Group 40").Activate Dim rng As Range With Worksheets("Group 40") Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With rng.Offset(0, 1).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,1,FALSE)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value rng.Offset(0, 2).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,2,FALSE)" rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value End Sub DELETE #N/A Rows On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _ EntireRow.Delete On Error GoTo 0 End Sub Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
When you change the cells from a formula to a value, then this will not
work: On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _ EntireRow.Delete On Error GoTo 0 You need to have it look for constants like this: On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlConstants, xlErrors) _ EntireRow.Delete On Error GoTo 0 -- Regards, Tom Ogilvy "STEVEB" wrote in message ... I tried to delete rows in a MACRO that are returned as #N/A after performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have to delete #N/A rows is not working & I was hoping someone has any suggestions. In my VLOOKUP the data is pasted as values, by doing so is the #N/A not recognized as an error? Should I combine the two MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows: VLOOKUP Worksheets("Group 40").Activate Dim rng As Range With Worksheets("Group 40") Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With rng.Offset(0, 1).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,1,FALSE)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value rng.Offset(0, 2).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,2,FALSE)" rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value End Sub DELETE #N/A Rows On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _ EntireRow.Delete On Error GoTo 0 End Sub Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
Don't you like the answer I give you in your other thread Steve???
Sorry Steve You add some things I see now after reading Tom's answer Please stay in you old thread next time -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "STEVEB" wrote in message ... I tried to delete rows in a MACRO that are returned as #N/A after performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have to delete #N/A rows is not working & I was hoping someone has any suggestions. In my VLOOKUP the data is pasted as values, by doing so is the #N/A not recognized as an error? Should I combine the two MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows: VLOOKUP Worksheets("Group 40").Activate Dim rng As Range With Worksheets("Group 40") Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With rng.Offset(0, 1).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,1,FALSE)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value rng.Offset(0, 2).Formula = _ "=VLOOKUP(A2," & _ "'[Reportable Accounts.xls]Sheet1'!" & _ "$A$2:$B$1147,2,FALSE)" rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value End Sub DELETE #N/A Rows On Error Resume Next ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _ EntireRow.Delete On Error GoTo 0 End Sub Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
Thanks Tom & Ron everything worked great, I appreciate upr help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
Thanks Tom & Ron everything worked great, I appreciate upr help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Delete #N/A rows Formula
Thanks Tom & Ron everything worked great, I appreciate upr help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 10:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com