Counting rows where a column has a value, any value except space/blank

[SJohnE]

I have a spread sheet that I need to run some data validations for. I am checking to see if a particular column is blank or not. First tried using countA for the column but it always returns a 1 even if I add several rows of data. then switch to counting rows and trying to iterate through checking certain fields to see if they have data. Nothing seems to work.

Code:
Public Sub MissingDataChk()
'Created by: Steve E'
'Create Date: 5/12/11
'Comments: This prcedure performs the following:
'1. Validates that required data is not missing
'______________________________________________________________________
Dim cC$
Dim vr As Variant
Dim vri As Variant
Dim i%, lastrow#
Dim xls As Worksheet

'initializes variables
Range("B1:b65536").Select ' for testing
MDF = False
lastrow# = 0 'initialize
lastrow# = Worksheets(1).Range("B1:B65536").Find("*", LookIn:=xlValues).Column 'method 1 not working returns a 2
lastrow# = Cells.Find("*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Column 'method 2 not working returns 34
lastrow# = Application.WorksheetFunction.CountA("B:B") 'method 3 not working returns a
lastrow# = WorksheetFunction.CountA("B1:B65536") 'method 4 not working returns a 1
MsgBox lastrow
i = 1
vr = Array("A" & i + 1, "B" & i + 1, "C" & i + 1, "E" & i + 1, "F" & i + 1, "P" & i + 1, "R" & i + 1, "U" & i + 1)
'Perform the check by row
Do While i <= lastrow
For Each vri In vr
If Range(vri).Value = "" Then
MsgBox "Required Data is Missing in " & vri & "." & vbCrLf & _
"Check all columns with Red headings." & vbCrLf & vbCrLf & _
"Note: If a column heading is red, it is required." & vbCrLf & vbCrLf & _
"Correct issue then try again.", vbCritical, "MISSING REQUIRED DATA"
Range(vri).Select
MDF = True
Exit For
End If
Next
i = i + 1
Loop
'Clean Up
cC$ = Empty
Set vr = Nothing
Set vri = Nothing
i% = Empty
lastrow# = Empty
Set xls = Nothing
End Sub

Column B of spread sheet has a header and in rows 2 - 7 has alphanumeric data.

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

hi,

First tried using countA for the column but it always returns a 1 even if I add several rows of data.

This is not a very helpful description of what your did and how you did it.

Please explain clearly, concisely and completely.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yooneek]

What about UsedRange.Rows.Count ?



OCD, it’s not obsessive if you can control it…

 

[SJohnE]

SkipVought,
See the code in my post for LastRow# = ... in the comment it is "Method 3" and "Method 4". Both methods returns only a 1 even though I have data from rows 1 - 7 of column B.

Yooneek,
Thanks for the sugestion, but it didn't work. The spread sheet has 100 rows where there are combo boxes to allow the user to enter 100 stock codes to be created at a time. I think this is seeing these plus some as the range because it returned 2000 when there is only 7 rows with data.

I don't understand what is going on, I have a different spread sheet where I use the CountA method and the Cells.Find... method and they both work perfectly. I think it must be the combo boxes screwing it all up.



S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

Each range needs to be fully qualified, else you are open to error.

ON THE SHEET, with a worksheet function, do you get this incorrect result? I would doubt it.

You must reference the "B:B" range to the sheet as...

dim rng as range

set rng = YourSheetObject.Range("B:B")

msgbox Application.COUNTA(rng)

Unless ALL ROWS within the used range have data in column B, COUNTA is not a good method to calculate the last row.

I would use...

With YourSheetObject.usedrange.currentregion
  lFirstRow = .row
  lLastRow = .row + .rows.count - 1
end with

Same concept for columns.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

SkipVaught,
Thank you for your input, I actuall just figured that out about
Set myRange = Worksheets("CTRL m TO PROCESS").Range("B1:B15")
lastrow = Application.WorksheetFunction.CountA(myRange)
I understand your point, column B is required, but if they meant to use row 10 for example and had it all filled in except for column B I would miss it in my method.

I have tried similar to your example of what should be used and for some reason I am getting a return of 2000 rows. I have even tried going in and highlighting all the rows below my range manually and deleting the rows with same result. would the used range see a combo box in a field as part of the range?


S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

if they meant to use

How can you program for INTENT; what is in someones mind???

Does your application have PRE_FILLED FORMULAS in unused rows?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

it does I figured it out. it was reading the hidden columns used to populate my combo boxes. I got it to work using some of what you gave me plus some of my own logic. see below:

'Initialize variables
blnkrow = 1
rowcnt = 0
lastrow = 0
'Find the last row of data
Do While blnkrow <> 0
rowcnt = rowcnt + 1
Set myrange = Worksheets("CTRL m TO PROCESS").Range("A" & rowcnt & ":AA" & rowcnt) 'set the range for the row Ax - AAx
blnkrow = Application.WorksheetFunction.CountA(myrange) 'Is there data in this row range
If blnkrow > 0 Then 'There is data, count the row
lastrow = lastrow + 1
End If
Loop

Thanks for all your help.

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

it does I figured it out.

What does "it does" refer to?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SJohnE]

sorry skipVought, I meant to replace that with, "It doesn't have prefilled formulas in unused rows, but it does have data in hidden columns that fill in down thrugh unused rows. they are used to populate the combo boxes".

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[SkipVought]

It doesn't have prefilled formulas in unused rows, but it does have data in hidden columns that fill in down thrugh unused rows. they are used to populate the combo boxes.

SAME DIFFERENCE!

It is a poor design, fraught with pitfalls.

If you were to use 1) Data list (97-2003) or Data > Insert > Table (2007+) and 2) Data > Validation -- LIST rather than Pre-entered Combobox OR a SINGLE Combobox that is made visible and positioned relative to a selection in the row, you could avoid anything in unused rows.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]