vba to copy cells where column is blank

jpar1983

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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
what do you want to do with the copied data?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top