Check if (part of) a row is blank.
It's no longer clear what the original OP is asking for. Everything
from Column B to the end of some data range? From Column B to Column IV?
From Column A to Column IV except for some specified column? Something
else?
In any event, =SUM(LEN(1:1))-LEN(D1)=0, array entered, will return
"TRUE" if A1:C1 and E1:IV1 are blank (whether or not D1 is blank),
"FALSE" otherwise.
If VBA code is needed instead of the formula
Sheets(2).Range("A65536").FormulaArray = "=sum(len(a1:iv1))-len(d1)=0"
MsgBox Range("A65536").Value
will return "True" of "False" under the same circumstances.
Alan Beban
Mika wrote:
Thanks Trevor, but do you know of a way that I could check
that columns B through...till the very "end" of the row
are all blank? This is so that the code is flexible no
matter how many colmns there are, it checks all the
columns are blank except one column (which may not
necessarily be the first). I guess I am also looking for
some function like "all of Row 1 - Column A" - kind of
like an interesect or union but a difference.
Hope that makes sense,
Mika
-----Original Message-----
Mika
one way:
Sub CheckBlankBtoF()
Dim LastRow As Long
Dim i As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Application.WorksheetFunction.CountBlank _
(Range(Range("B" & i), Range("F" & i))) = 5 Then
MsgBox "Range is Blank on Row " & i
End If
Next 'i
End Sub
Regards
Trevor
"mika." wrote in
message
...
How would one check if an entire row is blank?
Apparently
ActiveWorkbook.Worksheets("Ballot").Rows(4).nex t < ""
does not work.
As a follow up to that, I only want to check if, out of
columns A-F, whether B-F is blank for a particular row.
But I don't want to do this by column by column, I would
like to check the whole row (minus column A) in one
shot.
Is this possible? Any help would be greatly
appreciated.
Thanks,
Mika
.
|