Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to delete all rows containing #N/A
Hi I am recording a Macro which uses a VLookup to fill in values in colum A from another file. Where a match is not found it displays #N/A. can use AutoFilter to filter out all the #N/A's and then delete thes rows. The problem is that the orginal file is downloaded on a regula basis and the number of rows varies and therefore the number of #N/A's Is there macro code which will automatically delete all rows containin #N/A's. Thanks Shirle -- Shirley Munr ----------------------------------------------------------------------- Shirley Munro's Profile: http://www.excelforum.com/member.php...info&userid=83 View this thread: http://www.excelforum.com/showthread.php?threadid=40191 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to delete all rows containing #N/A
Hi Shirley,
Assume tha the #N/A values are in column A, then try: '=================== Public Sub TestA() On Error Resume Next 'In case no error values found! Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete On Error GoTo 0 End Sub '<<=================== Change A to suit. --- Regards, Norman "Shirley Munro" wrote in message news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com... Hi I am recording a Macro which uses a VLookup to fill in values in column A from another file. Where a match is not found it displays #N/A. I can use AutoFilter to filter out all the #N/A's and then delete these rows. The problem is that the orginal file is downloaded on a regular basis and the number of rows varies and therefore the number of #N/A's. Is there macro code which will automatically delete all rows containing #N/A's. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401910 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to delete all rows containing #N/A
Is this a multiple post? I'm sure I just answered this... Basically you need to use code and a loop too -- gearo ----------------------------------------------------------------------- gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=40191 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to delete all rows containing #N/A
Sample (Change to suit): (Looks for #N/A in Column B)
Sub DeleteNAs() lrow = Cells(Rows.Count, "B").End(xlUp).Row For i = lrow To 1 Step -1 If IsError(Cells(i, 2).Value) Then Cells(i, 2).EntireRow.Delete End If Next i End Sub "Shirley Munro" wrote in message news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com... Hi I am recording a Macro which uses a VLookup to fill in values in column A from another file. Where a match is not found it displays #N/A. I can use AutoFilter to filter out all the #N/A's and then delete these rows. The problem is that the orginal file is downloaded on a regular basis and the number of rows varies and therefore the number of #N/A's. Is there macro code which will automatically delete all rows containing #N/A's. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=401910 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to delete all rows containing #N/A
Norman Jones Wrote: Hi Shirley, Assume tha the #N/A values are in column A, then try: '=================== Public Sub TestA() On Error Resume Next 'In case no error values found! Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete On Error GoTo 0 End Sub '<<=================== Change A to suit. --- Regards, Norman "Shirley Munro wrote in message news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com... Hi I am recording a Macro which uses a VLookup to fill in values i column A from another file. Where a match is not found it displays #N/A. I can use AutoFilter to filter out all the #N/A's and then delet these rows. The problem is that the orginal file is downloaded on regular basis and the number of rows varies and therefore the number o #N/A's. Is there macro code which will automatically delete all row containing #N/A's. Thanks Shirley -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread http://www.excelforum.com/showthread...hreadid=401910 Nice work - specialcells! Though quite specific and hard to generalis -- gearo ----------------------------------------------------------------------- gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=40191 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete rows | Excel Discussion (Misc queries) | |||
Need a macro to delete rows | Excel Discussion (Misc queries) | |||
My Macro Won't Delete Rows?? | New Users to Excel | |||
macro to delete rows | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |