Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding cells missing formulas or data (Excel 2003)

    I have a fairly large worksheet (>1000 rows, 35 columns). Most cells should either have data, such as a date, or a formula. I'm looking for a way to check that, when the workbook is finally done, after numerous changes, additons and so on, I haven't forgotten to add or copy a formula, or that some other inadvertant mistake caused a cell to lose its formula or value. I know there is a property for that but I'm not sure how I can get to that via a formula. Can I get a macro to hide OK rows, ones which have the data type I need across all columns, thus showing me only those rows where a formula is missing in one of the required cells or a date value is missing? I could then see and fix those rows, rerun the macro until there are no rows showing, implying that all the cells tested either have a formula or a value. (and a way to unhide everything after that!) I have some primitive VBA skills, so I could edit a basic macro to have the particular cells and properties I need, if I could get some basic framework to start. TYIA!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cells missing formulas or data (Excel 2003)

    A simple method:
    - Select the entire range.
    - Select Edit | Go To...
    - Click Special...
    - Click the Blanks radio button.
    - Click OK.
    Excel will select all blank cells within the original selection.

    You can also use Format | Conditional Formatting... to highlight blanks, or cells that otherwise aren't up to spec.

    If you really want a macro, you'd need to tell us more about how it should check the worksheet.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding cells missing formulas or data (Excel 2003)

    I am not sure exactly what you need here. Could I ask you to attach a small section of your spreadsheet so that I can clearly understand what you are after. Usually there is no need for VBA to find missing data or cells with formulas. One can use Go To : Special to do this and then handle the missing data with appropriately.

    TX
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cells missing formulas or data (Excel 2003)

    I am aware of the goto special options and have some interesting macros that paint cells colors and so on. My concern is primarilly that I will miss something in row 856 column aa that throws everything off because I somehow never saw that blank or didn't have enough time available to peruse the workbook. Like everyone else, I have to work fast, and I'm thinking that by hiding all the OK rows I can then know for a fact there is something wrong with the row I see, even if it isn't visible in the columns right away, and I have to find it. I have attached a simplified concept worksheet. The real one has cell formulas as long as your arm in them and has too much confidential info to make redacting viable. This sample explains the issue: WHereverver you see the x I'm looking to have that row show until all the x's are gone and I know the row has complete data. Rows 2 and 6 of the example are complete with data. All the other rows have something important missing and should originally show as problem rows, then disappear as I fill in the missing data or formulas. Thanks again.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cells missing formulas or data (Excel 2003)

    I'm not sure it is a good idea, but you could put the following code in the worksheet module (right-click the sheet tab and select View Code from the popup menu to open this module):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const m = 16 ' Last column to be checked
    Dim oCell As Range
    Dim r As Long
    Dim c As Long
    Dim f As Boolean
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each oCell In Target
    r = oCell.Row
    f = True
    For c = 1 To m
    If Application.WorksheetFunction.IsError(Cells(r, c)) = False Then
    If Cells(r, c) = "" Then
    f = False
    Exit For
    End If
    End If
    Next c
    If f = True Then
    oCell.EntireRow.Hidden = True
    End If
    Next oCell
    ExitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Change the value of the constant m as needed.

Posting Permissions

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