Checking for blank cells

[ammdumas]

Have a macros that takes a previous row of info and copies it to the next row. I want to add an if statment to it that checks for blank cells before it does the copy thing.

Here's what I tried. Basically, what I'm trying to write is select last row, check for columns for blanks, if blank, msg saying stop, otherwise, copy the row.

myrow = Range("A65536").End(xlUp).row 'select last available row
If Range("A" & myrow & ":L" & myrow).Value = "" Then
MsgBox "Add Data to Columns A through L before adding a new row", vbCritical, "Shop Drawing Summary"
Else
'Add a new row
I get a type mismatch on the 'if' line. Help would be appreciated.

 

[Von Pookie]

I think your problem is that you're trying to check the value of a range. It doesn't quite work that way.

Another way to go about it:

Sub test()
Dim myrow As Range

Set myrow = Range("A65536").End(xlUp) 'select last available row
If WorksheetFunction.CountA(Range(myrow, myrow.Offset(0, 11))) = 0 Then
MsgBox "Add Data to Columns A through L before adding a new row", vbCritical, "Shop Drawing Summary"
Else
'Add a new row
End If

End Sub

This counts the number of cells in the range A:L that have values. If it =0 that means there is nothing entered in that range, and your message box should appear.

Edit: Forgot the HTML wasn't working. Fixed.

 

[Norie]

Do you want to check columns B-L in the last row for blanks?

Maybe this will work:

Sub CheckBlanks()
Dim LastRow As Long
Dim bBlanks As Boolean
Dim I As Integer
Dim rng As Range

    LastRow = Range("A65536").End(xlUp).Row

    Set rng = Range("B" & LastRow)

    For I = 1 To 9
        bBlanks = bBlanks Or rng <> ""
        Set rng = rng.Offset(I, 0)
    Next I

    If bBlanks Then
        MsgBox "Add Data to Columns A through L before adding a new row", vbCritical, "Shop Drawing Summary"
    Else
        ' add new row
    End If
End Sub

 

[ammdumas]

I should have clarified somewhat. I have already set 'myrow' as an integer because I use it to populate certain cells once the row has been copied.

I tried to circumvent this by make von pookie's code to check the line; myrowchk as range. But the chk seems to get skipped. This is what I have. I have added 'new to the new stuff I added

Dim myrowchk As Range 'new
Dim myrow As Integer
If ActiveSheet Is Sheets("TRANSSUMMARY") Then
Set myrowchk = Range("A65536").End(xlUp) 'new
If WorksheetFunction.CountA(Range(myrowchk, myrowchk.Offset(0, 11))) = 0 Then 'new
MsgBox "Add Data to Columns A through L before adding a new row", vbCritical, "Shop Drawing Summary" 'new
Else
myrow = Range("A65536").End(xlUp).row
ActiveSheet.Unprotect
Rows(Range("A65536").End(xlUp).row).Copy Range("A65536").End(xlUp).Offset(1, 0)
Range("a65536").End(xlUp).Value = Range("F2").Value & "-" & Range("a65536").End(xlUp).row - 7
myrow = Range("A65536").End(xlUp).row - 1
etc. etc.

I can't really change myrow because there's already lots of code using it and working. Any ideas?

 

[Norie]

Have you tried stepping through your code using F8 to check it?

By the way you should probably declare myrow as Long.

 

[Von Pookie]

This seems to be working for me. I rewrote your code a little; once you have set the variables, you can use them throughout the code. So instead of using Range("A65536").end(xlup) over and over again, you can use the variable myrowchk instead, since you already set it to the last cell in column A:

Sub test()

Dim myrowchk As Range, myrow As Long

If ActiveSheet.Name = "TRANSSUMMARY" Then
    Set myrowchk = Range("A65536").End(xlUp)
    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
        myrow = Range("A65536").End(xlUp).Row
        ActiveSheet.Unprotect
        Rows(myrow).Copy Destination:=Rows(myrow + 1)
        myrowchk.Value = Range("F2").Value & "-" & myrow - 7
        myrow = myrow - 1
    End If
End If

End Sub

 

[ammdumas]

Yay! That did the trick. Yes I know. I'm messy when it comes to coding. I'm the copy-and-paste king. Thanks for all the help. Cheers!

 

[ammdumas]

Crap. Turns out that there is one column that can be blank (the seventh). How do I modify the line;

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

to ignore the 7th column? It's a new line of code I've never played with and I'm not sure how to fit an OR/AND statment in there.

 

[ammdumas]

Tried your line Erik.Van.Geit...

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

and it didn't work. Erased some other cells from columns that have to have data and it still created the new line. Unfortunately, due to my company, I can't install that HTML maker thingy to show you what I'm talking about. But here are the columns headings anyways (in order from A to L)...

TRANS #
Review Number
Date
Shop Drawing Number
Recipient
Email
RC?
RACI Status
Sender
Days to Review
Return Date
Action Required

Only 'RC?' can be blank when a new line is added via the routine. All other cells must be populated with something. I really appreciate all the help.

 

[Von Pookie]

This is basically the same thing Erik posted, just coded differently:

Sub test2()
Dim myrowchk As Range, myrow As Long, chk As Long

If ActiveSheet.Name = "TRANSSUMMARY" Then
    Set myrowchk = [A65536].End(xlUp)
    myrow = myrowchk.Row
    
    chk = WorksheetFunction.CountA(Range(Cells(myrow, 1), _
    Cells(myrow, 6)), Range(Cells(myrow, 8), Cells(myrow, 12)))
    
    If chk < 11 Then
        MsgBox "Add Data to Columns A through L before adding a " _
        & "new row", vbCritical, "Shop Drawing Summary"
    Else
        ActiveSheet.Unprotect
        Rows(myrow).Copy Destination:=Rows(myrow + 1)
        myrowchk = [F2] & "-" & myrow - 7
        myrow = myrow - 1
    End If
End If

End Sub