Macro to find blank cells, copy row, paste into another worksheet, then delete

Hi,

So I have 3 columns of data (Column A, B, C). Column C has some blank cells in it. I would like to be able to find all the blank cells in Column C, then copy the rows (Column A, B, C) which contain the blank cells, paste them into another sheet within the same workbook, then delete them from the original sheet.

using the macro recorder I was able to do this:

Sub Macro4()
'
' Macro4 Macro
'
'
Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

Obviously, this finds the cells which are blank & allows me to delete those rows containing the blank cells in the SAME worksheet. I still have not figured out the way to copy them to another sheet before deleting them.

Is the current macro a good starting point to expand on, or should I try a totally different approach?

Thanks in advance,
Rob

Hi,

Something like this:

Please Login or Register  to view this content.

Like this:

Please Login or Register  to view this content.

The "=" sign for Criteria1 tells the filter to look for "blanks"

And, you're welcome!

Originally Posted by xladept

Like this:

<SNIP CODE>

The "=" sign for Criteria1 tells the filter to look for "blanks"

And, you're welcome!

xladept - so if I'm just using Excel formulas, the way I'd look for blanks is 2 quotation marks (ie, ""). So in VBA, the analogous symbol is the equal sign (ie, =)? OK, thanks for the clarification!!!

Oh, and again, thank you for showing me how to do it 2 different ways: 1 way is to create a new sheet, and the other way is to add data to an existing sheet.

I tried both, and both work as you had "advertised." :-) However, what about error checking? For instance, say I've ran the macro on a file, and it has blanks - thus, it will copy those rows & paste them to another sheet. However, if I run it again on another file & that file has no blanks, I will get an error - is there a way to have it give a message (perhaps to the screen) which says "no blank data" or something similar?

Also, upon discussing the path forward w/ members of the team, it was suggested that since there will be some data files which do not contain blank data (some files do, some don't - it all depends on if the data collecting tool is up & running during the time the information is being logged to the file or not) - perhaps the original macro which creates a sheet if there are blank cells, then couple that with a display of a message saying "no blank data" if there are no blank cells? That seems like the best of both worlds, and will take into account files which do contain blank cells, as well as files which do not contain blank cells.

I (and I know others who are curious about doing something similar to me) REALLY appreciate the help, and further appreciate the explanation of what is going on - perhaps one day, because of the knowledge folks like you are imparting to us, we can be here helping other "newbies" :-)

THANK YOU!!

Two quotation marks actually signify a null field - the "blanks" category is both nulls and blanks - the ="=" only works with autofilter - keep using the two double quotes when looking for nulls

I'll be looking into the "no blanks" situation anon.

Here's a routine that checks to see whether a filter is necessary and, if not, issues a message:

Please Login or Register  to view this content.

BTW - Do you want to stack the extracts rather than just write over them??

You may be running it without a "NO_DATA" sheet - try this:

Please Login or Register  to view this content.

BTW - It's expected that there is a sheet named NO_DATA in your workbook?

Originally Posted by xladept

You may be running it without a "NO_DATA" sheet - try this:

<SNIP CODE>

BTW - It's expected that there is a sheet named NO_DATA in your workbook?

xladept - sorry, no. Perhaps I failed to explain, please allow me to try again. Originally, I was thinking that there would always be blank cells, and if that were the case, then it would make sense to always have a sheet in Excel named "NO_DATA." However, upon meeting & discussing w/ other members of the team (some of whom are more familiar w/ the data collection tool than I am), it was discovered that sometimes there will be files with NO blanks in them, and sometimes there will be.

This revelation prompted a change in my thinking - which was to have the Excel file start off NOT having a sheet named "NO_DATA" then, upon running the macro on the data, if blanks DO exist, then create a sheet called "NO_DATA" and paste the rows into it. However, if blanks do NOT exist, just present the message to the screen which says "No blank fields to filter."

Also, at this point, I'm not sure if the end-user is going to copy & paste, thus creating a new Excel file each time they run the macro, or if they are going to run it over & over on the same file. If they are going to run it over & over, then the next time, if blanks exist, the macro can append the new data to the end of the existing data & keep moving down the rows from there. If no blanks exist, then nothing will get written to the file.

To me, that was the "best of both worlds" because it provided a solution for both instances (blanks, & no blanks), as well as allowed the user the choice to use a new Excel file each time, or keep using the "old" one.

Hopefully I have cleared up any ambiguities this may have caused, and I apologize if I wasn't more clear earlier.

Thank you.

Try this version - it should create the output sheet if necessary or append to it if present:

Please Login or Register  to view this content.

You're welcome! I'm especially pleased that you feel that you've learned from me