Range of Cells Containing Text (2002 SP-2)

Topic

New Reply

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.

Reply | Quote

Replies

You mean randomly spread cells?
No CurrentRegion or UsedRange?

Reply | Quote

You mean randomly spread cells?
No CurrentRegion or UsedRange?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

I prefer SpecialCells Method when it will work

---

[/indent]

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

I prefer SpecialCells Method when it will work

---

[/indent]

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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”).

Reply | Quote

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

Thanks,
Steve

Reply | Quote

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

Thanks,
Steve

Reply | Quote

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”).

Reply | Quote

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

Reply | Quote