Testing for Blank Cells

**shg** · 10-02-2009, 03:54 PM

We've now come full circle. Here's the original formula applied to that data:

      --A-- -B-- -C-- --D-- E --------------------F--------------------
  1    234A N333 4T41  7657 4 E1 and down: =COUNTA(A1:B1)*COUNTA(C1:D1)
  2    11C2       321  3T27 2                                          
  3         L954  989       1                                          
  4              56D1 297-1 0                                          
  5   87000 P199            0                                          
  6    D992                 0                                          
  7    Z222      3391       1

Rows 1, 2, 3, & 7 are non-zero.

**msy12** · 10-02-2009, 04:35 PM

Okay...

It seems to me, you will have to actually write a macro to paste each individual row into a new workbook or worksheet. I'll try to mess with how to put that all together over some time...but try to get it yourself as well...

So far...I think this is what you'll need to get started...

=IF(COUNTA(A1),IF(COUNTA(C1),"2",IF(COUNTA(D1),"2","1")),IF(COUNTA(B1),IF(COUNTA(C1),"2",IF(COUNTA(D1),"2","0")),"0"))

Paste that at the end of 1st row (E1, for example), and then drag it down. That will count whether there are values in A1 & C1, A1 & D1, B1 & C1, or B1 & D1...in that order. If any are true, they return "2". Once you have the "2", you will be able to write a macro to extract rows with a "2" in column E:E.

I hope that leads you in the right direction...

**CaptinKrunch** · 10-04-2009, 11:52 AM

Shg,

Thanks for your help. I have a couple additional questions. Sorry for not being Excel savy.

Your formula for the testing the data does work. Our spreadsheet has 18 columns of data and over 12,000 rows of data. I wish to use your formula to test each row of data and when there is a match copy the entire row to another spreadsheet. How can I set up you formula to read each row without having to define the last row of data? Also, is there a way to copy each row of data as a whole row rather than on a cell by cell basis?

Thanks

**shg** · 10-04-2009, 12:52 PM

Post a sanitized example of the data, but having representative contents?

Also, does the data get copied to another sheet in the same workbook, and if so, does it replace what's already there? Or another workbook?

All the particulars, please ...

**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!