Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range of Cells Containing Text (2002 SP-2)

    What's an efficient way, in VBA, to determine the smallest range of cells (single selection rectangle) on a worksheet that encompasses all the non-blank cells. In my case all the cells contain text, but it might be helpful if this could also apply if the the cells might contain formulas or numbers.

    Thanks.

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    You mean randomly spread cells?
    No CurrentRegion or UsedRange?
    Regards,
    Martin

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

    Re: Range of Cells Containing Text (2002 SP-2)

    Identifying the Real Last Cell contains a neat function to determine the last populated cell. You can easily modify this to determine the first populated cell. The first and last cells define the rectangular range you want. Here is the complete code:

    Function FirstCell(ws As Worksheet) As Range
    Dim LastRow As Long, LastCol As Long
    On Error Resume Next
    With ws
    LastRow = .Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
    End With
    Set FirstCell = ws.Cells(LastRow, LastCol)
    End Function

    Function LastCell(ws As Worksheet) As Range
    Dim LastRow As Long, LastCol As Long
    On Error Resume Next
    With ws
    LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    End With
    Set LastCell = ws.Cells(LastRow, LastCol)
    End Function

    Function SingleUsedRange(ws As Worksheet) As Range
    Dim fs As Range, ls As Range
    On Error Resume Next
    Set SingleUsedRange = ws.Range(FirstCell(ws), LastCell(ws))
    End Function

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    Use the SpecialCells Method of the object.

    These will select all the cells with constants, formulas, the constant cells with numbers, and the formulas resulting in text

    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ).Select
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , xlNumbers).Select
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues).Select

    There are other combinations and variants possible. You can use something like:
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
    For Each rcell In rng
    'your code here
    Next

    To loop ONLY thru the appropriate cells in the range.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    Martin, Hans, Steve, John:

    Thanks, all, for your quick responses. Each of your suggestions appear to solve the problem I posed. Martin's "UsedRange" property certainly seems to be the simplest and most straightforward solution (bordering on the "Well, Duh!" department) -- I just overlooked it when scanning the Object Browser.

    But Steve's suggestion is clairvoyant -- I didn't mention that I need to "process" each "used" cell in the range and this solution allows me to process only the used cells. You've even included the shell for stepping through those cells in the SpecialCells range.

    Thanks again!

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

    Re: Range of Cells Containing Text (2002 SP-2)

    Don't thank me; I withdrew the post because the code doesn't always work as I expected. However, you may still find:

    Union(Activesheet.SpecialCells(xlCellTypeConstants , 23),Activesheet.SpecialCells(xlCellTypeFormulas, 23))

    to be of use in looping though cells.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Range of Cells Containing Text (2002 SP-2)

    UsedRange is, of course, by far the easiest. BUT be aware that Excel doesn't always keep this property up-to-date when the worksheet is being edited. Excel 2002 does a much better job at it than previous versions, but I don't know how reliable UsedRange is.

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

    Re: Range of Cells Containing Text (2002 SP-2)

    As a further explanation to HansV's comment, through Exel 2000, UsedRange will include all cells that are formatted in other than the default Style format, and so may include a vast range of Formatted but Empty cells. You can test this in the immediates window with

    ActiveSheet.Usedrange.Select

    then look at what has been selected on the sheet. Looping through lots of empty cells can slow down your code, so I prefer SpecialCells Method when it will work.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    <hr>I prefer SpecialCells Method when it will work<hr>

    In what circumstances do you see it not working? There are times you may get a runtime error if there are no cells of the "type" (and you will have to account for this in code) You can do UNIONS of the various ranges if desired, or even work on each separately.

    Formatting-wise you can work on ALL the formulas (eg) with one command without having to "loop" thru them all, just set the format of the entire range. It can make for much speedier code. Even if you have to break each "special cell" type into a separate loop, and do them all individually, it should still be faster than going thru them all.

    It also has the advantage of "built-in" validation: if you are going to compare the cells to a value, you don't have to worry about checking to make sure it is the right type before comparing (to avoid a "type mismatch" which excel ignores in formulas better than VBA does), you know what the selection is by what you selected.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    Note that the special cells method has a bug. If the number of cells that fits the description exceeds 8192, Excel VBA gives no error and selects insufficient cells. If you use F5, Excel tells you there are too many ("Range too large").
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Range of Cells Containing Text (2002 SP-2)

    I use it whenever I can. Perhaps I should have said "whenever it fits" There are times when you want to run a Find, or run Selection.NumberFormat = something, and it's not necessary to use SpecialCells.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range of Cells Containing Text (2002 SP-2)

    Jan,
    I was unaware of this "bug" and will have to keep it in mind.

    Thanks,
    Steve

Posting Permissions

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