Count rows On error MsgBox

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

Robert Christie

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