Check if (part of) a row is blank.
 

How would one check if an entire row is blank?
Apparently
ActiveWorkbook.Worksheets("Ballot").Rows(4).next < ""
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

Check if (part of) a row is blank.
 

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).next < ""
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

Check if (part of) a row is blank.
 

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).next < ""
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


.

Check if (part of) a row is blank.
 

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("IV" & i))) = 255 Then
MsgBox "Range is Blank on Row " & i
End If
Next 'i
End Sub

--
Regards,
Tom Ogilvy


Mika wrote in message
...
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).next < ""
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


.

Check if (part of) a row is blank.
 

set rng = Range("A1").End(xltoright)
if rng.Column = 256 and isemtpy(rng) then
' empty to the right of cell A1

rw = 1
if isempty(cells(rw,256)) then
if cells(rw,256).End(xltoLeft).column = 1 then
' empty to the right of cell A1

--
Regards,
Tom Ogilvy

mika. wrote in message
...
How would one check if an entire row is blank?
Apparently
ActiveWorkbook.Worksheets("Ballot").Rows(4).next < ""
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

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


.