Copying and Filtering Values

[dcunningham]

Hello All,

I'm trying to copy a range of values from one sheet to another, and then filter out any NA() errors. So far, I have the following bit of code:

Sub CopyExtQuery()' Copies the query for the extended report from the Calculations sheet. Filters out NA() errors after copying.
    Sheets("Calculations").Range("C56:C2600").Value = Sheets("Extended Report").Range("A9:A2553").Value
    Sheets("Calculations").Range("G56:G2600").Value = Sheets("Extended Report").Range("E9:E2553").Value
    Application.CutCopyMode = False
    Range("A8:D8").Select
    ActiveSheet.Range("$A$8:$H$2553").AutoFilter Field:=1, Criteria1:="#N/A"
    Rows("9:9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A8:H8").Select
    ActiveSheet.ShowAllData
    Selection.AutoFilter
End Sub

This works, sort of. The issue is it's a one and done sort of deal. The range being copied is the result of some calculations on a changing query of a database which determine whether or not I want the values to be copied (returning an NA() code if I want them excluded). The way I'm filtering the NA() codes is by filtering column A to just the NA() values and deleting those rows. The deleting of the rows also deletes the merging of the cells that I have, with A:D being merged and E:H being merged from row 9 down to row 2553. The same number of cells are merged in the Calculations sheet, and for the same number of rows. So, when it's first run it works, but when I try to run it again there's not enough space to complete the copying of the values which results in nothing being copied over.

Any suggestions on a better way to get what I'm trying to do done?

Thanks,

Dan

 

[oldbrewer]

a simple macro to run down the column that contains NA() values and delete that row ?

 

[dcunningham]

a simple macro to run down the column that contains NA() values and delete that row ?

Hi oldbrewer,

I tried doing this and it seems to work, but now a dialogue box is produced that says: "There's already data here. Do you want to replace it?". It seems like what's happening is that when I recorded the Macro there were two stages to the paste: one to paste the formatting and another to paste the values. So, the formatting is pasted first, filling the cells with data, and then the values are pasted and Excel's asking if I want to replace the values entered from pasting the formatting. When I hit "Yes" it's all good, but I have to do this manually and would rather the user of this document not have to do so. Is there a way to combine the copying of the formatting and the values into one command? Here's what my code looks like at present:

Sub CopyToCalc()

    'Copies data from Calculation sheet


    Sheets("Extended Report").Select
    Range("A9:H2553").ClearContents
    Sheets("Calculations").Select
    Range("C56:J2600").Copy
    Sheets("Extended Report").Select
    Range("A9:D9").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Range("A9:D9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    'Filters out NA() errors
        
    Range("A8:H8").Select
    Range("A8:H8").AutoFilter
    ActiveSheet.Range("A8:H2553").AutoFilter Field:=1, Criteria1:="#N/A"
    Range("A9:H2553").Select
    Range("A9:H2553").EntireRow.Delete
    ActiveSheet.Range("A8:H2553").AutoFilter Field:=1
    Range("A8:H8").Select
    Range("A8:H8").AutoFilter
    
End Sub

Regards,

Dan

 

[dcunningham]

Actually, I figured something out on my own. I just turned off alerts while copying and pasting. The resulting code looked like this:

Sub CopyToCalc()

    'Copies data from Calculation sheet, clears old data


    Sheets("Extended Report").Select
    Range("A9:H2553").ClearContents
    Sheets("Calculations").Select
    Range("C56:J2600").Copy
[COLOR=#ff0000]    Application.DisplayAlerts = False[/COLOR]
    Sheets("Extended Report").Select
    Range("A9:D9").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Range("A9:D9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
[COLOR=#ff0000]    Application.DisplayAlerts = True[/COLOR]
        
    'Filters out NA() errors
        
    Range("A8:H8").Select
    Range("A8:H8").AutoFilter
    ActiveSheet.Range("A8:H2553").AutoFilter Field:=1, Criteria1:="#N/A"
    Range("A9:H2553").Select
    Range("A9:H2553").EntireRow.Delete
    ActiveSheet.Range("A8:H2553").AutoFilter Field:=1
    Range("A8:H8").Select
    Range("A8:H8").AutoFilter
    
End Sub

It now works like I want it to.

Dan

 

[oldbrewer]

great !!!