Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Blank Cells (Excel 97)

    I am trying to get a macro that will run on blank cells. I note the ISBLANK() function for worksheets but can not seem to get it to work in a Macro. How can I use an if Statement to work on only blank cells.
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Blank Cells (Excel 97)

    Cell-by-cell you can use

    cell.Value = ""

    but also see if

    range.SpecialCells(xlCellTypeBlanks)

    which will select all blank cells in the specifed range, helps, bearing in mind that you have to handle the situation where SpecialCells finds no blanks.

    HTH, if not post back.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Cells (Excel 97)

    It depends on what you mean by "blank cells." If you mean cells that are empty, then you could use an IF statement like this:

    <pre> If ActiveSheet.Range("A1").Value = "" Then
    </pre>


    If you mean cells that contains only blank characters, then you could use:

    <pre> If (ActiveSheet.Range("A1").Value <> "") And (Trim(ActiveSheet.Range("A1").Value) = "") Then
    </pre>


    If you mean both, then you could use:

    <pre> If Trim(ActiveSheet.Range("A1").Value) = "" Then
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Blank Cells (Excel 97)

    Thanks for your input.
    I thought there was a distintion between a cell that may have been cleared and Therefor "" and a cell that had never been used, which is what I thought I needed to use. I think I understand the distinction between "" and " " ("[space]").

    BTW where can you find info on things like you mentioned like SpecialCells and xlCellTypeBlanks. I spent lots of time searching thru all the help files and niether of those showed up.

    Thanks again.
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Blank Cells (Excel 97)

    Halek, it's best to learn VBA by starting out with a book and asking questions the Lounge. <!post=Here's,180035>Here's<!/post> a thread where some books were discussed. The SpecialCells methods are all VBA equivalents of Go To, Special alternatives. Record a bunch of sample macros with Go To Special and you'll get a feel for the capabilities. Good luck.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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