Using VBA to copy and paste rows containing #N/A into another sheet

[ThePangloss]

I'm very new to VBA, so I'm not sure how to do this.

I have a Sheet1 containing names and such. In the 6th column there numbers and #N/A's scattered across. I'd like to be able to remove all the #N/A's and paste them to a separate sheet for analysis on them without having the filled cells in that column move with them.

I'd like to move it to Sheet3 if possible, so it'd be something like this on Sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Day[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Work#[/TD]
[/TR]
[TR]
[TD]doe[/TD]
[TD]john[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]1990[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]mary[/TD]
[TD]29[/TD]
[TD]4[/TD]
[TD]1928[/TD]
[TD]22829[/TD]
[/TR]
[TR]
[TD]marley[/TD]
[TD]bob[/TD]
[TD]21[/TD]
[TD]3[/TD]
[TD]1960[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]duck[/TD]
[TD]sam[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]1992[/TD]
[TD]29282[/TD]
[/TR]
</tbody>[/TABLE]

then after applying the code on Sheet3 I'd like to have
[TABLE="width: 500"]
<tbody>[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Day[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Work#[/TD]
[/TR]
[TR]
[TD]doe[/TD]
[TD]john[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]1990[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]marley[/TD]
[TD]bob[/TD]
[TD]21[/TD]
[TD]3[/TD]
[TD]1960[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

and it would continue down for all rows in Sheet1 which is about 7k


Anyone have any advice?

 

[MARK858]

Assuming you mean your sheet tabs names are Sheet1 and Sheet3 and you aren't referring to the sheets codename try (and hopefully you are using 2010 upwards)...

Sub Filterit()
    With Sheets("Sheet1").Range("F1:F" & Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="#N/A"

        On Error Resume Next
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow
            .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Delete
        End With
        On Error GoTo 0
        .AutoFilter
    End With
End Sub

 

[ptownbro985]

Several ways to do it. Since you're a self described "newbie", here's one that's easy to follow for a newbie.

    Sheets("Sheet1").Select
    Range("$A$1:$F$5").AutoFilter Field:=6, Criteria1:="#N/A"
    Range("$A$1:$F$5").Copy
    
    Sheets("Sheet3").Select
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("Sheet1").Select
    ActiveSheet.ShowAllData    'or Cells.AutoFilter = False to remove filter

This works, but is fairly static. In a final solution you may want to make it more dynamic and maybe add error protection by doing things like:
1. Don't need to really select "Sheet 1" or "Sheet 3". You could just effect them directly.
2. Instead of explicitly naming the range to filter... find the range, or used named range (like other suggestion), or use variable to hold the range "string", etc..

 

[ThePangloss]

Assuming you mean your sheet tabs names are Sheet1 and Sheet3 and you aren't referring to the sheets codename try (and hopefully you are using 2010 upwards)...

Sub Filterit()
    With Sheets("Sheet1").Range("F1:F" & Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="#N/A"

        On Error Resume Next
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow
            .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Delete
        End With
        On Error GoTo 0
        .AutoFilter
    End With
End Sub

I ran the macro and Excel keeps not responding afterwards. Anything I can do to fix that? I'm running 2010.

Edit: It says that the .AutoFilter part is what failed

 

[ThePangloss]

Nevermind! I saw Sheet3 and it had those up. Thanks a lot to both of you for the replies. ptbrownbro, do you know of any good sites or books to learn VBA to use in excel? It seems very useful to use and I'd like to have a better grasp and understanding of what's going on in the code.

 

[ptownbro985]

Wow. Sorry. Been so long since I first started I have no recommendations on where to learn. However, I highly recommend learning. It's amazing what you can do when you get the hang of it. And how much time you can save.

 

[MARK858]

@ThePangloss, You will possibly have had problems if you ran it a second time as your column wouldn't have any #N/A to find, might need to move the error check up just in case (btw you probably didn't realize the data moved as Sheet3 doesn't get selected as it slows code down badly and 99% of the time is unnecessary).

It seems very useful to use and I'd like to have a better grasp and understanding of what's going on in the code.

Hopefully the bits in green will help you understand better

Sub Filterit()
'applies autofilter to column F from F1 to last cell in column F using criteria of #N/A
    With Sheets("Sheet1").Range("F1:F" & Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="#N/A"
        
        'Bypasses error if specialcells is nothing (might have to move this up above the autofilter row
        On Error Resume Next
        
        'offset(1) moves the range down 1 row so the header isn't copied, resize makes the range 1 row shorter
        'because the offset has added an extra line.
        'SpecialCells(xlCellTypeVisible) is just that, the visible cells in column F. The
        'with statement is just to save retyping the line multiple times
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow
        
        '.Copy is copying the range above.
        'Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)is finding the next
        'blank cell in column A of Sheet3 to send the data to
            .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
         
         'deletes the rows
            .Delete
        
        'closes the with statement
        End With
        
        'resets the error check
        On Error GoTo 0
        
        'turns the autofilter off
        .AutoFilter
        
        'Closes the with statement
    End With
End Sub

or used named range (like other suggestion)

Just to clarify that I not using a "named range" in the other suggestion. I am using a normal range and a last rows variable just in a single row basically...

LastRow = = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow)

put into a single row.

P.S. I also wouldn't give an OP a code copying as Values unless they specifically ask for it


@ThePangloss, if Hiker95 comes on line I will see if I can get a link to his most recent list of reading material

 

[MARK858]

@ThePangloss, a link to hiker95's most recent list is below.

hiker95's List