Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for Non Blank and Blank Cells Before Save | Excel Discussion (Misc queries) | |||
If Match Part of Text Within Cell, Then Blank | Excel Worksheet Functions | |||
need macro to check part# and sum | Excel Worksheet Functions | |||
Looping Macro That adds a blank row between different part #'s | Excel Discussion (Misc queries) |