Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    San Jose, California, USA
    Posts
    82
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Zero vs. blank cell check in VBA for Excel

    I have an excel spreadsheet that can have a number of rows with a zero in column 10 ("quantity") and some rows with text subheaders where the quantity cell is BLANK.


    I'm trying to develop a macro to delete the rows where the quantity cell is zero but it also deletes the subheader row. It seems to me that the simple test I'm doing does not discriminate between zero and blank.


    If Worksheets(1).Cells(lnRowCount, 10) = 0 Then
    rnSelection.Rows(lnRowCount).Delete



    Is there a better check method to do this. I guess a couple of nested IFs might work:
    If Isblank then skip else if zero then delete.


    Is there a more elegant way?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Damnlimey,

    Try this code:
    Code:
    Option Explicit
    
    Sub ZeroBlank()
    
       Dim lCntr As Long
       
       For lCntr = 1 To 4
          If Cells(lCntr, 1) = 0 Then
            If Cells(lCntr, 1) = "" Then
              Debug.Print "Cell Row A" & Format(lCntr, "#") & " is BLANK"
            Else
              Debug.Print "Cell Row A" & Format(lCntr, "#") & " = ZERO"
            End If
       End If
       Next lCntr
       
    End Sub
    ZeroOrBlank.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •