Look Who it Was Random video

Excel - VBA Macro to Copy all Non-Blank Cells in Column to New Column - Episode 779

4,903 views · Published 30 January 2009 · 3:10 · Indexed 1 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Copy All Non-Blank Cells to New Column.

Welcome back to the MrExcel netcast where we discuss all things Excel. I'm Bill Jelen and today we have a great question from one of our viewers. They were trying to write a VBA code that would find all the non-blank cells in a specific column and copy them to another column. This can be a tedious task, but luckily, there is a simple solution using the macro recorder.

To start, we will turn on the macro recorder by going to TOOLS, MACRO, RECORD NEW MACRO. We will name the macro "GETCODES" and make sure it is stored in the workbook. Next, we will use the built-in functionality of the auto filter to easily find all the non-blank cells in the desired column. By selecting the "non-blanks" option in the filter, we can quickly see all the codes in that column.

Now, we will select all the cells in that column and use CONTROL+C to copy. The beauty of using the auto filter is that it only copies the visible cells, making our task much easier. We will then select a cell in the desired destination column, for example, K1, and paste the copied cells. Once we turn off the auto filter, our task is complete and we can stop recording the macro.

If we take a look at the code generated by the macro recorder, we can see that it turns on the auto filter, selects the desired column, copies the cells, and then pastes them in the desired destination. However, we can make this code even more efficient by eliminating the need to select and paste. By simply copying the entire column and specifying the destination as range K1, we can achieve the same result in one step.

In conclusion, the macro recorder is a powerful tool that can save us a lot of time and effort when writing VBA code. In this case, it provided us with a simple solution using Excel's built-in functionality, rather than having to write code from scratch. I hope you found this tip helpful and be sure to tune in next time for more Excel tips and tricks from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Using VBA to find non-blank cells in a column
(00:19) Example data and desired outcome
(00:29) Using the macro recorder to create the code
(00:39) Turning on the macro recorder
(00:54) Turning on the auto filter
(01:09) Copying and pasting the non-blank cells
(01:25) Turning off the auto filter and stopping the recording
(01:35) Testing the code
(01:52) Shortening the code
(02:05) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Copy visible cells in Excel with autofilter
Creating a unique list of codes in Excel
How to turn on autofilter in Excel
Leveraging Excel's built-in functionality to solve problems
Pasting data in a specific cell in Excel
Running a macro in Excel
Selecting and copying a column in Excel
Turning off autofilter in Excel
Understanding the code generated by the macro recorder
Using autofilter instead of looping through rows in Excel
Using the macro recorder in Excel
VBA code to find non-blank cells in column I

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152121/


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.

More from this channel