I am pretty new to VBA, so this may be obvious, but I can't figue it
out. Like they say, there are no stupid questions, only stupid people
that ask them.

Let me describe my sheet first.

I have a form for data input. There is a column header row in row 1
and a row number column in column A. The data starts in B1, five
columns and 30 rows. I have a named range(called "range")that
references B2:F31. There is no data to the right or below the table.
The data input may be less than 30 items, so all the rows may not be
full. I need to check to see if all the columns in the used rows have
data in them. I have tried several variations using
SpecialCells(xlCellTypeLastCell) to determine the used range to no
avail. For example,

range(range("b2"),range("range").SpecialCells(xlCe llTypeLastCell)).select

selects B2:H31. Why it goes out to column H I don't know. I set up a
new spreadsheet with a simplified version of my form and it did the
same thing, selecting 2 columns to the right of my last column. I am
not so much interested in why that happened as I am in just being able
to determine the used part of my range

In a nutshell:

I have a range B2:F31
A varying number of rows will have data, the remaining will be blank.
All the blank rows will be at the bottom, all filled rows at the top,
starting with cell b2.
I want to check the range B2:F?? to see if there are any blank cells
using COUNTBLANK or something simlilar. I just can't selct the correct
range.

Can anyone give me any insight? Thanks.

Do I understand you correctly or am I one of the stupid people ?

blankcells=0
nonblankcells=0
myRange=Range("B2:F31")
For each c in myRange
if isblank(c.value) then blankcells = blankcells + 1 else nonblankcells
= nonblankcells + 1
Next

Chris

"HBS" wrote in message
om...
I am pretty new to VBA, so this may be obvious, but I can't figue it
out. Like they say, there are no stupid questions, only stupid people
that ask them.

Let me describe my sheet first.

I have a form for data input. There is a column header row in row 1
and a row number column in column A. The data starts in B1, five
columns and 30 rows. I have a named range(called "range")that
references B2:F31. There is no data to the right or below the table.
The data input may be less than 30 items, so all the rows may not be
full. I need to check to see if all the columns in the used rows have
data in them. I have tried several variations using
SpecialCells(xlCellTypeLastCell) to determine the used range to no
avail. For example,

range(range("b2"),range("range").SpecialCells(xlCe llTypeLastCell)).select

selects B2:H31. Why it goes out to column H I don't know. I set up a
new spreadsheet with a simplified version of my form and it did the
same thing, selecting 2 columns to the right of my last column. I am
not so much interested in why that happened as I am in just being able
to determine the used part of my range

In a nutshell:

I have a range B2:F31
A varying number of rows will have data, the remaining will be blank.
All the blank rows will be at the bottom, all filled rows at the top,
starting with cell b2.
I want to check the range B2:F?? to see if there are any blank cells
using COUNTBLANK or something simlilar. I just can't selct the correct
range.

Can anyone give me any insight? Thanks.

I don't want to check every cell in B2:F31. For example, if I input 6
lines of data, I would want to check B2:F7. B8:F31 would be blank.
This could change every time the form is used. I could write some
loops that would cycle through and check, but that seemed overly
complicated.



"Chris Leonard" wrote in message ...


blankcells=0
nonblankcells=0
myRange=Range("B2:F31")
For each c in myRange
if isblank(c.value) then blankcells = blankcells + 1 else nonblankcells
= nonblankcells + 1
Next

Chris

"HBS" wrote in message
om...
I am pretty new to VBA, so this may be obvious, but I can't figue it
out. Like they say, there are no stupid questions, only stupid people
that ask them.

Let me describe my sheet first.

I have a form for data input. There is a column header row in row 1
and a row number column in column A. The data starts in B1, five
columns and 30 rows. I have a named range(called "range")that
references B2:F31. There is no data to the right or below the table.
The data input may be less than 30 items, so all the rows may not be
full. I need to check to see if all the columns in the used rows have
data in them. I have tried several variations using
SpecialCells(xlCellTypeLastCell) to determine the used range to no
avail. For example,

range(range("b2"),range("range").SpecialCells(xlCe llTypeLastCell)).select

selects B2:H31. Why it goes out to column H I don't know. I set up a
new spreadsheet with a simplified version of my form and it did the
same thing, selecting 2 columns to the right of my last column. I am
not so much interested in why that happened as I am in just being able
to determine the used part of my range

In a nutshell:

I have a range B2:F31
A varying number of rows will have data, the remaining will be blank.
All the blank rows will be at the bottom, all filled rows at the top,
starting with cell b2.
I want to check the range B2:F?? to see if there are any blank cells
using COUNTBLANK or something simlilar. I just can't selct the correct
range.

Can anyone give me any insight? Thanks.

OK, exit the loop when you get a blank cell then ....

myRange=Range("B2:F31")
For each c in myRange
if isblank(c.value) then
exit for
else
DO SOMETHING
end if
Next


Hope this is what you're after.

Chris

"HBS" wrote in message
om...
I don't want to check every cell in B2:F31. For example, if I input 6
lines of data, I would want to check B2:F7. B8:F31 would be blank.
This could change every time the form is used. I could write some
loops that would cycle through and check, but that seemed overly
complicated.



"Chris Leonard" wrote in message
...


blankcells=0
nonblankcells=0
myRange=Range("B2:F31")
For each c in myRange
if isblank(c.value) then blankcells = blankcells + 1 else
nonblankcells
= nonblankcells + 1
Next

Chris

"HBS" wrote in message
om...
I am pretty new to VBA, so this may be obvious, but I can't figue it
out. Like they say, there are no stupid questions, only stupid people
that ask them.

Let me describe my sheet first.

I have a form for data input. There is a column header row in row 1
and a row number column in column A. The data starts in B1, five
columns and 30 rows. I have a named range(called "range")that
references B2:F31. There is no data to the right or below the table.
The data input may be less than 30 items, so all the rows may not be
full. I need to check to see if all the columns in the used rows have
data in them. I have tried several variations using
SpecialCells(xlCellTypeLastCell) to determine the used range to no
avail. For example,


range(range("b2"),range("range").SpecialCells(xlCe llTypeLastCell)).select

selects B2:H31. Why it goes out to column H I don't know. I set up a
new spreadsheet with a simplified version of my form and it did the
same thing, selecting 2 columns to the right of my last column. I am
not so much interested in why that happened as I am in just being able
to determine the used part of my range

In a nutshell:

I have a range B2:F31
A varying number of rows will have data, the remaining will be blank.
All the blank rows will be at the bottom, all filled rows at the top,
starting with cell b2.
I want to check the range B2:F?? to see if there are any blank cells
using COUNTBLANK or something simlilar. I just can't selct the correct
range.

Can anyone give me any insight? Thanks.