| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
| Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
| Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
| If formula result is false, how do I auto-delete that rows? | Excel Worksheet Functions | |||
| How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Linear Mode
