Change Line of code that finds blank cells

[ammdumas]

Have an IF statement which which stops an 'add a row' routine if any cell in the first 11 columns are blank (of the row being checked). However, in my haste, one of the columns that it is checking CAN be blank. How do I modify the IF stement to ignore the 7 column out of the 11 it is checking. It's not a simple OR statement. I figured that one out...

If WorksheetFunction.CountA(Range(myrowchk, _
myrowchk.Offset(0, 11))) < 11 Then
MsgBox "Add Data to Columns A through L before adding a " _
& "new row", vbCritical, "Shop Drawing Summary"
Else
'add a row rountine

 

[erik.van.geit]

ammdumas,

Do you mean that you have to check 11 - 7 = 4 cells to be blank ?

this example is checking columns 3, 5, 6 and 8

Sub test()
rtc = 1
If WorksheetFunction.CountA(Cells(rtc, 3), Cells(rtc, 5), Cells(rtc, 6), Cells(rtc, 8)) < 4 Then
MsgBox "Add Data to Columns A through L before adding a " _
& "new row", vbCritical, "Shop Drawing Summary"
Else
End If
End Sub

kind regards,
Erik

 

[ammdumas]

No.

Originally it was "Stop routine if any cells in columns 1 through 11 are blank". I actually got this code from Von Pookie and it works great...until I found out that it has to ignore the 7th column (cells in 7th column CAN be blank). I think her code uses COUNTA and inconjunction, specifies the number of columns to check (i.e. 11) so that

If WorksheetFunction.CountA(Range(myrowchk, myrowchk.Offset(0, 11))) < 11 Then

basically says in the line I'm checking, if anything less than all 11 column are 'not blank', then stop.

Because I have to ignore column seven, this function becomes too simplistic.

I think I read that right.

 

[erik.van.geit]

to be sure:
A to L are 12 columns
so when column 7 is not to be checked indeed there have to be 11 arguments

If WorksheetFunction.CountA(Range("A" & rtc & ":F" & rtc), Range("H" & rtc & ":L" & rtc)) < 11 Then

will this do what you want ?
kind regards,
Erik

 

[Von Pookie]

Please continue in your original thread:
http://www.mrexcel.com/board2/viewtopic.php?t=129352