Is this easy, or a challenge for someone

[bont]

I have an application that opens up any generic Excel application.

I currently cycle through each worksheet, and want to apply formatting to any cell that has data in it on the worksheet.

How can I detect only the cells that have data in them and assign them to a range, i.e. range(A1,B4,C5:F100...) or something a little more generic.

My current functions enterprit the data being modified as a range, so I would like to know how to tie these together.

In addition:

I had orignally had the user specify the range to check on each worksheet, i.e. A1:Q700, but this current range show here is causing an overflow problem on this line of code;

i_NumberofCells = i_NumberofCells + m_objWorksheet.Range(Text2.Text).Cells.Count

any ideas how to fix this?

 

[Chattin]

Would the CurrentRegion property help?

Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help

 

[VBslammer]

For your overflow problem, try changing the Integer variable to Long - Integer overflows at 32K.

VBSlammer

Unemployed in Houston, Texas

 

[bont]

I have tried this statement:

Set m_objRange = m_objWorksheet.UsedRange.CurrentRegion

The only problem I Have is that it doesn't grab all the cells. If I have an excel with data in Rows 1, 2, 6, & 7, it will only see the data in rows 1 & 2 as row 3 is empty.

How can I use currentregion to grab all the possible ranges in the worksheet?

As far as my overflow, I changed to long and it still had problems. The numbers being used didn't look to be so large. If I get the above question solved, I don't have to worry about the overflow as badly.

 

[bont]

I have found a way to make it work using specialcells; however, I would still like to get it to work with only cells that have value being selected? This would ensure best speed and precision.

Any help?

 

[acron]

You can use m_objWorksheet.UsedRange (i.r omit CurrentRegion) to select the used range of the worksheet. However this will include blanks. Probably SpecialCells would be fast way to create teh range as in :

 Dim CellsUsed As Range, m_objWorksheet As Worksheet
 Set m_objWorksheet = Sheets([i]SheetName[/i])
 Set CellsUsed = Union(m_objWorksheet.Cells.SpecialCells(xlCellTypeFormulas), _
                          m_objWorksheet.Cells.SpecialCells(xlCellTypeConstants))

A.C.
Are you traying to select all cells with any non-blank value, or all cells with a specific value ?

 

[VBslammer]

[tt]

Sub TestRanges(ByVal strSheet As String)
Dim strRanges() As String
Dim i As Integer

strRanges = GetRanges("Sheet1&quot

For i = 0 To UBound(strRanges)
Debug.Print strRanges(i)
Next i

End Sub

Function GetRanges(ByVal strSheet As String) As String()
On Error GoTo ErrHandler
Dim sht As Worksheet
Dim r As Range
Dim a As Range
Dim strRanges() As String
Dim lngFound As Long

Set sht = Sheets(strSheet)

Set r = Union(sht.Cells.SpecialCells(xlCellTypeConstants), _
sht.Cells.SpecialCells(xlCellTypeFormulas))

ReDim strRanges(r.Areas.Count - 1)

For Each a In r.Areas
On Error Resume Next
strRanges(lngFound) = a.Name.Name
If Err <> 0 Then
On Error GoTo ErrHandler
a.Name = UCase(sht.Name) & &quot;_&quot; & Replace(a.Address(False, False), &quot;:&quot;, &quot;_&quot
strRanges(lngFound) = a.Name.Name
End If
lngFound = lngFound + 1
Next a

GetRanges = strRanges

ExitHere:
Exit Function
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere
End Function

[/tt]


VBSlammer

Unemployed in Houston, Texas