Testing for Blank Cells

**CaptinKrunch** · 10-04-2009, 02:08 PM

Shg,

Thanks for the formula. I'm sorry that I didn't understand the first time around. I do have additional questions. We have a spreadsheet that being used to collect data. It presently has over 12,000 rows, 18 columns wide. I wish to use your formula to test each row and it there is a match, copy the row to another spreadsheet. How can I set up the formula to to read the 12,000 rows without naming the final row so that if more rows are added to the formula does not have to be modified? Also, is there an faster way to copy the entire row when there is a match rather than each cell of the row?

Thanks!

**shg** · 10-04-2009, 02:10 PM

I'll help with that. Would you please respond to my prior post?

**CaptinKrunch** · 10-04-2009, 03:11 PM

Shg,

I'm sorry, I didn't see your reply and I've been having some problems logging onto the website. If there is a match of data using your formula, I wish to copy the row of data to another sheet in the same workbook. Attached is sample data. The columns that we are testing if blank or not at L, M, O, & P so your formula would look like this =COUNTA(L1:M1)*COUNTA(O1:P1).

Thanks!

**shg** · 10-04-2009, 05:30 PM

Try this:

Sub x()
    Dim r           As Range
    Dim cell        As Range

    Set r = Worksheets("Sheet1").UsedRange
    With r.Offset(, r.Columns.Count).Resize(, 1)
        .FormulaR1C1 = "=counta(rc12:rc13)*counta(rc15:rc16)"

        For Each cell In .Cells
            If cell.Value Then
                Intersect(cell.EntireRow, r.EntireColumn).Copy _
                        Destination:=Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
            End If
        Next cell

        .ClearContents
    End With
End Sub

**CaptinKrunch** · 10-05-2009, 04:35 PM

Shg,

I am not familiar with the code that you posted. I've never used anything like this. Please let me know how and where do I use this. Sorry for not being Excel savy.

Thanks!

**shg** · 10-05-2009, 04:46 PM

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

Then,
1. Do Alt+F8 to open the macro dialog
2. Select the macro name (x in this case) from the dropdown list and press Run

**CaptinKrunch** · 10-05-2009, 05:21 PM

Shg,

I received the following error after I pressed "RUN". I am not sure what this means or what to do.
Thanks!

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Expected End Sub
---------------------------
OK Help
---------------------------