Column I is sparsely filled in with product codes. You would like a macro to copy only the non-blank cells to a new column. It turns out that the macro recorder can actually reliably perform this task. Episode 779 will show you how.
This video shows how to record the GetCodes macro to copy only the non-blank cells from a column to a new column.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video shows how to record the GetCodes macro to copy only the non-blank cells from a column to a new column.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Great question came in.
The person was trying to write some VBA code that would go through and find all the non-blank cells in column I and write them to another column -- let's say, column K -- and you'll see here in our data, most of these records are blank but, every once in a while, we have a code that's over here, and I love this particular macro because this is one macro that the macro recorder can actually get correct.
So, I'm going to turn on the macro recorder.
TOOLS, MACRO, RECORD NEW MACRO.
I'll call it GETCODES, no space of course in the MACRO NAME, stored in THIS WORKBOOK, we’ll click OK.
Well, we're going to use some built-in functionality here.
I'm going to turn on the auto filter -- DATA, FILTER, AUTOFILTER -- and then, in column I, all the way at the bottom, there's a couple of interesting options -- one called blanks and one called non-blanks.
I'm going to choose the non-blanks, and you'll see that we have all the codes there.
Now I'll select all of column I and use CONTROL+C to copy.
Now, interestingly enough, because the autofilter is turned on, we've copied only the visible cells.
I need to select one other cell -- for example cell K1 -- and paste, and I'll get just those cells.
Now, we’ll come back here and turn off the autofilter and, at this point, stop recording, and we'll do a little test here.
So, let me delete that data over in column K. I'll fill in a few other values here and run the code.
So, ALT+F8, choose RUN, and, sure enough, we get our unique list of codes over there in column K.
Taking a quick look at the code, it turns on the autofilter, it always goes to field 9 and looks for the criteria non-blank, selects the whole column, does a copy, and then we selected K1, it does a paste, and turns off the auto filter.
Now, we could shorten this up just a little bit.
For example, instead of selecting the column and then copying the selection, we could just copy the column, and we could also specify destination as range K1.
We don't have to bother to select K1, don't have to bother to paste.
That would all happen in one fell swoop, but still, the macro recorder, even without editing the macro, comes up with something that's going to work very easily.
I think when the person asked the question, they were figuring that they were going to have to loop through every single row, but this is one case where the autofilter has the perfect built-in functionality, and rather than write code from scratch, we could just use Excel’s built-in functionality to solve the problem.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Great question came in.
The person was trying to write some VBA code that would go through and find all the non-blank cells in column I and write them to another column -- let's say, column K -- and you'll see here in our data, most of these records are blank but, every once in a while, we have a code that's over here, and I love this particular macro because this is one macro that the macro recorder can actually get correct.
So, I'm going to turn on the macro recorder.
TOOLS, MACRO, RECORD NEW MACRO.
I'll call it GETCODES, no space of course in the MACRO NAME, stored in THIS WORKBOOK, we’ll click OK.
Well, we're going to use some built-in functionality here.
I'm going to turn on the auto filter -- DATA, FILTER, AUTOFILTER -- and then, in column I, all the way at the bottom, there's a couple of interesting options -- one called blanks and one called non-blanks.
I'm going to choose the non-blanks, and you'll see that we have all the codes there.
Now I'll select all of column I and use CONTROL+C to copy.
Now, interestingly enough, because the autofilter is turned on, we've copied only the visible cells.
I need to select one other cell -- for example cell K1 -- and paste, and I'll get just those cells.
Now, we’ll come back here and turn off the autofilter and, at this point, stop recording, and we'll do a little test here.
So, let me delete that data over in column K. I'll fill in a few other values here and run the code.
So, ALT+F8, choose RUN, and, sure enough, we get our unique list of codes over there in column K.
Taking a quick look at the code, it turns on the autofilter, it always goes to field 9 and looks for the criteria non-blank, selects the whole column, does a copy, and then we selected K1, it does a paste, and turns off the auto filter.
Now, we could shorten this up just a little bit.
For example, instead of selecting the column and then copying the selection, we could just copy the column, and we could also specify destination as range K1.
We don't have to bother to select K1, don't have to bother to paste.
That would all happen in one fell swoop, but still, the macro recorder, even without editing the macro, comes up with something that's going to work very easily.
I think when the person asked the question, they were figuring that they were going to have to loop through every single row, but this is one case where the autofilter has the perfect built-in functionality, and rather than write code from scratch, we could just use Excel’s built-in functionality to solve the problem.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
