vba to copy cells where column is blank

jpar1983

New Member
Joined
Aug 11, 2012
Messages
23
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:
A
B
C
D
E
data1
data2
data3
data4
data5
data1
data2
data3
data4
data5
data1
data2
data3
data4
data5
data1
data2
data1
data2
data1
data2
data1
data2
data3
data4
data5
data1
data2
data3
data4
data5
data1
data2
data3
data4
data5

<TBODY>
</TBODY>

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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,853
Office Version
2010
Platform
Windows
what do you want to do with the copied data?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,721
Office Version
365
Platform
Windows
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.
Code:
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

New Member
Joined
Aug 11, 2012
Messages
23
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,721
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 11, 2012
Messages
23
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

New Member
Joined
Aug 11, 2012
Messages
23
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,268
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top