vba to copy cells where column is blank

[jpar1983]

Hello all, long time lurker first time poster... This website and others have been really helpful to me in the past and I'm looking for a little more help now.
I am looking for vba code that will allow me to copy cells in columns A and B that correspond to empty cells in columns C, D, and E.
Columns A and B are many many rows of continuous data that is contstantly being added to. Columns C, D, and E are also being added to, but there are always blanks relative to columns A and B. Let me 'splain:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
[TR]
[TD]data1
[/TD]
[TD]data2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[TD]data5
[/TD]
[/TR]
</TBODY>[/TABLE]

So what I want to do is copy the rows in Columns A and B that are blank in Columns C, D and E. Or you could even limit it to just Column C being blank, because D and E will also be blank in those situations.

This is one part of my evil plan to... ahem, I mean part of a far larger macro that will aid me in... stuff. Thanks for any help! Oh and I am working in Excel 2010 with a Windows 7 PC.

 

[JoeMo]

what do you want to do with the copied data?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is the sort of thing you want.

I've assumed that there are headings in row 1. If not, your code could insert some first. As Joe mentioned you haven't said where you want the data copied to so I've just copied it to column J on the same sheet.

Sub Copy_Data()
    Application.ScreenUpdating = False
    With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
        .AutoFilter Field:=3, Criteria1:=""
        .Resize(, 2).Copy Destination:=Range("J1")
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[jpar1983]

I can't get to the spreasheet right now, but I can tell you that I am copying the data to another workbook. I have code that will open and format the other workbook and then switch back to this one, so if what you have works then all I need to do is retool the destination coding. I stayed away from autofilters before because I have bad luck with them (lack of experience, I know, catch 22 right?) As for the headings, they actually start in row 4 or 5 but if I change your initial range to match that row ("A4") that should work right? Also, will this copy columns A and B, or is this just selecting the entire row? Either one is fine, A and B will be the only data in the row anyway. Thanks!

 

[Peter_SSs]

I can't get to the spreasheet right now, but I can tell you that I am copying the data to another workbook. I have code that will open and format the other workbook and then switch back to this one, so if what you have works then all I need to do is retool the destination coding.

Sounds like you are on the right track.

As for the headings, they actually start in row 4 or 5 but if I change your initial range to match that row ("A4") that should work right?

Yes, just make that refer to the heading cell in column A

Also, will this copy columns A and B, or is this just selecting the entire row? Either one is fine, A and B will be the only data in the row anyway. Thanks!

The code only copies columns A:B.

After initially using columns A:C to do the AutoFilter on column C the
.Resize(, 2).Copy
reduces the range to two columns to copy

 

[jpar1983]

Now I'm having trouble with the destination coding - I want it to copy to another workbook with the same sheet names, and I would like it to copy from whatever range it is already selecting and paste to the second workbook starting in cell A5. How do I tell it to switch workbooks? The workbook I need will already be open.

 

[jpar1983]

Scratch that, I've got it now.

 

[jpar1983]

Another puzzler, that really isn't that big of a deal but I'm curious why it is happening - on these sheets that I am copying data from I included buttons at the top which will access this macro. I have about a dozen or so sheets, each with a button at the top to access this macro for each individual sheet. Works like a charm. Then I created one massive macro that will do all of them at the same time. Which also seems to work fine, except that for some reason when I do it that way it also pastes a copy of that button over to the other workbook, macro linked to it and everything. I did not change the coding, at least not that part of the coding, and am puzzled as to how that button is being copied each time. Not a big deal, I save them all individually so I can just delete it when I do, just a curiosity. Thanks for your help on this one!