Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Remove #n/a
I have a macro that places #n/a's in rows, the next step for it to do is
delete them. I would like it to delete out the WHOLE rows that get populated #n/a, not columns. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Remove #n/a
I believe this code will work for you. Don't be confused by the fact that I
have a column defined to do some testing on - that's necessary. In the end, entire rows with #N/A in that column (you may redefine it as needed in your workbook) will be absolutely deleted - not just cleared out, but physically deleted. Since this is a destructive action, I suggest you first make a backup copy of your workbook and put the code into either the backup or original and try it to make sure it's doing what you want. That way, if something goes wrong you'll still have an original copy of the workbook to fall back to. To put this code into your workbook; open the workbook, press [Alt]+[F11] to open the VB Editor, choose Insert | Module from the VBE menu and then copy and paste the code into that module. Edit the column letter assigned to constant testColumn as needed for your workbook. To use it, select the sheet to be cleaned up, then use Tools | Macro | Macros and choose this one from the list and click [Run]. Sub DeleteNARows() 'pick a column to test in, this 'column should be one that will have '#N/A error displayed in it and that 'goes as far down the sheet as you need 'to examine for the #N/A conditions 'although not all entries have to be #N/A 'just something in them to the end ' Using column E for this example ' as E was where I put a VLOOKUP() formula ' to test/generate #N/A errors. Const testColumn = "E" ' change as required 'no other changes to make Dim naRowList As String Dim anyRange As Range Dim anyCellEntry As Range 'find area to examine: Set anyRange = ActiveSheet.Range(testColumn & "1:" & _ ActiveSheet.Range(testColumn & _ Rows.Count).End(xlUp).Address) 'make list of rows that are to be deleted For Each anyCellEntry In anyRange If anyCellEntry.Text = "#N/A" Then naRowList = naRowList & anyCellEntry.Row & _ ":" & anyCellEntry.Row & "," End If Next 'remove the extra comma at the end of the list of rows 'that are to be deleted (if list is empty, exit sub) If naRowList < "" Then naRowList = Left(naRowList, Len(naRowList) - 1) Else 'no #N/A entries found Set anyRange = Nothing Exit Sub End If Set anyRange = ActiveSheet.Range(naRowList) anyRange.Delete Shift:=xlUp Set anyRange = Nothing End Sub "Schwimms" wrote: I have a macro that places #n/a's in rows, the next step for it to do is delete them. I would like it to delete out the WHOLE rows that get populated #n/a, not columns. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Remove #n/a
Are you placing these #n/a's in a single column?
Maybe you can just select that column Edit|goto|special constants and errors (nothing else checked) edit|delete|entire row (xl2003 menus) Schwimms wrote: I have a macro that places #n/a's in rows, the next step for it to do is delete them. I would like it to delete out the WHOLE rows that get populated #n/a, not columns. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Remove #n/a
Awesome,
Thanks guys, both work like a charm. "Schwimms" wrote: I have a macro that places #n/a's in rows, the next step for it to do is delete them. I would like it to delete out the WHOLE rows that get populated #n/a, not columns. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro 4.remove | Excel Discussion (Misc queries) | |||
Macro to Remove Macro Text | Excel Discussion (Misc queries) | |||
Remove Macro Moduel | Excel Discussion (Misc queries) | |||
how do i remove password from xls vba macro? | New Users to Excel | |||
remove macro | New Users to Excel |