Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to tell if a range has text in it (excel xp)

    I'm writing a macro and need to look through a range of cells to see if there is text in them. Do i use ISTEXT(Range("A1:A100").value) or something like that? thank you

  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: How to tell if a range has text in it (excel xp)

    The following will work, I'm guessing you want the first one, but remember to handle the error 1004 if none are found:

    range.SpecialCells(xlCellTypeConstants, 2)

    range.SpecialCells(xlCellTypeFormulas, 2)
    -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: How to tell if a range has text in it (excel xp)

    The code below will tell you how many cells in A1:A100 contain text.

    <pre>Dim iCnt As Integer
    iCnt = 0
    On Error Resume Next
    iCnt = Worksheets("Sheet1").Range("A1:A100").SpecialCells (xlCellTypeConstants, xlTextValues).Count
    On Error GoTo 0
    MsgBox iCnt & " Cells contain text."
    </pre>

    Legare Coleman

Posting Permissions

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