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

ThePangloss

New Member
Joined
Jun 19, 2015
Messages
40
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

LastFirstDayMonthYearWork#
doejohn1021990#N/A
janemary294192822829
marleybob2131960#N/A
ducksam98199229282

<tbody>
</tbody>

then after applying the code on Sheet3 I'd like to have
LastFirstDayMonthYearWork#
doejohn1021990#N/A
marleybob2131960#N/A

<tbody>
</tbody>

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


Anyone have any advice?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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)...

Code:
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

New Member
Joined
Apr 3, 2012
Messages
17
Several ways to do it. Since you're a self described "newbie", here's one that's easy to follow for a newbie.

Code:
    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

New Member
Joined
Jun 19, 2015
Messages
40
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)...

Code:
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

New Member
Joined
Jun 19, 2015
Messages
40

ADVERTISEMENT

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

New Member
Joined
Apr 3, 2012
Messages
17
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@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

Rich (BB code):
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...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top