Check if (part of) a row is blank.

  • Thread starter Thread starter mika.
  • Start date Start date
M

mika.

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top