Macro help

[JasmineL]

I need to write a macros that will search my worksheet for blank cells in column A. If the search reveals a blank cell, I want the row containing the the blank cell cut from the existing worksheet and pasted to a new worksheet that I already have creating in my macros...can anyone help?

 

[Smitty]

Deleting blank rows is easy:

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

But how do you copy blank rows to a new sheet (and why)?

 

[JasmineL]

Smitty,

Thank you for providing the code for deleteding the rows...this will be helpful.

I must not have provided a clear explanation of what I'm trying to do. I want to search column A for blank cells. If cell A2, for example, is blank, then I want to copy row 2 (which contains data in the other columns) to a new worksheet.

Does this explain things more clearly?

Jasmine

 

[jmthompson]

Are the cells truly blank or are there formulas listed within?

 

[Smitty]

OK, see if this does what you want:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>        <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>            <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1)<br>                <SPAN style="color:#00007F">If</SPAN> LenB(Cells(i, "A").Value) = 0 <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> Cells(i, "A").EntireRow<br>                        .Copy LastRow.Offset(, -1)<br>                        .Delete<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[JasmineL]

The cells are truly blank.

 

[JasmineL]

I'm having issues still with the macro...because I'm a dork.

On worksheet "new term" I have three rows with data in all cells. On the fourth row, cell A4 does not have any data (but the rest of the columns within that row do have data). I need to cut row 4 from the "new term" worksheet to another worksheet called "new duplicate term"

I need to repeat this step for the entire file, cutting rows without data in column A from the "new term" worksheet and pasting them into the "new duplicate term" worksheet.

In addition, I have one row with the name Jane Doe that I also need cut from the "new term" worksheet and paste into the "new duplicate term" worksheet.