Count rows On error MsgBox
 

Hi
I need some lines of code to count the number of rows in
column A which includes blank cells.
If number of rows is greater than, less than 10 a MsgBox
with instructions should appear.
I'm importing a .txt file, deleting any blank rows and
adding/changing text and some formatting. When finished
the text should finish on row 10, ranging from cell A1 to
N10 no-more no-less.
The purpose is to indicate to the user, the original .txt
file did not contain 10 rows of text.
These lines of code will run at the end of macro.

I'm not sure how the Count, If true, if false, then, else
syntax should be laid out, and call up the MsgBox.

TIA

Bob C.

Count rows On error MsgBox
 

Hi Robert,

here is some code

Sub AnyBlanks()

If
Application.Evaluate("MAX((A1:A1000<"""")*ROW(A1: A1000))-COUNTA(A:A)") < 0
Then
MsgBox "There are empty cells"
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Christie" wrote in message
...
Hi
I need some lines of code to count the number of rows in
column A which includes blank cells.
If number of rows is greater than, less than 10 a MsgBox
with instructions should appear.
I'm importing a .txt file, deleting any blank rows and
adding/changing text and some formatting. When finished
the text should finish on row 10, ranging from cell A1 to
N10 no-more no-less.
The purpose is to indicate to the user, the original .txt
file did not contain 10 rows of text.
These lines of code will run at the end of macro.

I'm not sure how the Count, If true, if false, then, else
syntax should be laid out, and call up the MsgBox.

TIA

Bob C.

Count rows On error MsgBox
 

Hi Bob
Sorry, in my post the first line was misleading.
The mention of blank cells was to indicate the rows of
data in column A contained blank cells, not I wanted to
know if blank cells where there. The overall count of rows
should be 10 including blank cells ie A1 to last row
containing text has to count up to 10 even if say cells
A9 and A10 are blank.

HTH

Regards Bob C.


-----Original Message-----
Hi Robert,

here is some code

Sub AnyBlanks()

If
Application.Evaluate("MAX((A1:A1000<"""")*ROW
(A1:A1000))-COUNTA(A:A)") < 0
Then
MsgBox "There are empty cells"
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Christie"
wrote in message
...
Hi
I need some lines of code to count the number of rows
in
column A which includes blank cells.
If number of rows is greater than, less than 10 a
MsgBox
with instructions should appear.
I'm importing a .txt file, deleting any blank rows and
adding/changing text and some formatting. When finished
the text should finish on row 10, ranging from cell A1
to
N10 no-more no-less.
The purpose is to indicate to the user, the
original .txt
file did not contain 10 rows of text.
These lines of code will run at the end of macro.

I'm not sure how the Count, If true, if false, then,
else
syntax should be laid out, and call up the MsgBox.

TIA

Bob C.



.

Count rows On error MsgBox
 

Dim rng as Range
set rng = Range("A1").CurrentRegion

if rng.Address(0,0) < "A1:N10" Then
msgbox "Bad data, data should fill A1:N10"
End if


or

Dim rng as Range
set rng = cells(rows.count,1).End(xlup)
if rng.row < 10 then
msgbox "Should be 10 rows of data"
End if


--
Regards,
Tom Ogilvy


"Robert Christie" wrote in message
...
Hi Bob
Sorry, in my post the first line was misleading.
The mention of blank cells was to indicate the rows of
data in column A contained blank cells, not I wanted to
know if blank cells where there. The overall count of rows
should be 10 including blank cells ie A1 to last row
containing text has to count up to 10 even if say cells
A9 and A10 are blank.

HTH

Regards Bob C.


-----Original Message-----
Hi Robert,

here is some code

Sub AnyBlanks()

If
Application.Evaluate("MAX((A1:A1000<"""")*ROW
(A1:A1000))-COUNTA(A:A)") < 0
Then
MsgBox "There are empty cells"
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Christie"
wrote in message
...
Hi
I need some lines of code to count the number of rows
in
column A which includes blank cells.
If number of rows is greater than, less than 10 a
MsgBox
with instructions should appear.
I'm importing a .txt file, deleting any blank rows and
adding/changing text and some formatting. When finished
the text should finish on row 10, ranging from cell A1
to
N10 no-more no-less.
The purpose is to indicate to the user, the
original .txt
file did not contain 10 rows of text.
These lines of code will run at the end of macro.

I'm not sure how the Count, If true, if false, then,
else
syntax should be laid out, and call up the MsgBox.

TIA

Bob C.



.

Count rows On error MsgBox
 

Thanks Tom

All bases covered with your reply.
Thanks also for book info on a previous post.
With a Birthday coming up, I think John Walkenbach will
be on my bookshelf very soon.

Regards
Bob C.

-----Original Message-----
Dim rng as Range
set rng = Range("A1").CurrentRegion

if rng.Address(0,0) < "A1:N10" Then
msgbox "Bad data, data should fill A1:N10"
End if


or

Dim rng as Range
set rng = cells(rows.count,1).End(xlup)
if rng.row < 10 then
msgbox "Should be 10 rows of data"
End if


--
Regards,
Tom Ogilvy


"Robert Christie"
wrote in message
...
Hi Bob
Sorry, in my post the first line was misleading.
The mention of blank cells was to indicate the rows of
data in column A contained blank cells, not I wanted to
know if blank cells where there. The overall count of
rows
should be 10 including blank cells ie A1 to last row
containing text has to count up to 10 even if say cells
A9 and A10 are blank.

HTH

Regards Bob C.


-----Original Message-----
Hi Robert,

here is some code

Sub AnyBlanks()

If
Application.Evaluate("MAX((A1:A1000<"""")*ROW
(A1:A1000))-COUNTA(A:A)") < 0
Then
MsgBox "There are empty cells"
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Robert Christie"

wrote in message
...
Hi
I need some lines of code to count the number of
rows
in
column A which includes blank cells.
If number of rows is greater than, less than 10 a
MsgBox
with instructions should appear.
I'm importing a .txt file, deleting any blank rows
and
adding/changing text and some formatting. When
finished
the text should finish on row 10, ranging from cell
A1
to
N10 no-more no-less.
The purpose is to indicate to the user, the
original .txt
file did not contain 10 rows of text.
These lines of code will run at the end of macro.

I'm not sure how the Count, If true, if false, then,
else
syntax should be laid out, and call up the MsgBox.

TIA

Bob C.



.



.