If you want to avoid formulas that return blanks give this a try...
Public Sub test()
Dim rng As Range
Set rng = GetNonBlanks(Sheets("Sheet1"))
If Not rng Is Nothing Then rng.Select
End Sub
Public Function GetNonBlanks(ByVal wks As Worksheet) As Range
Dim rngConstants As Range
Dim rngFormulas As Range
Dim rngTemp As Range
Dim rng As Range
With wks.Cells
On Error Resume Next
Set rngConstants = .SpecialCells(xlCellTypeConstants)
Set rngFormulas = .SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rngFormulas Is Nothing Then
For Each rng In rngFormulas
If Trim(rng.Value) <> "" Then
If rngTemp Is Nothing Then
Set rngTemp = rng
Else
Set rngTemp = Union(rng, rngTemp)
End If
End If
Next rng
Set rngFormulas = rngTemp
End If
If rngConstants Is Nothing And rngFormulas Is Nothing Then
Set GetNonBlanks = Nothing
ElseIf Not rngConstants Is Nothing And rngFormulas Is Nothing Then
Set GetNonBlanks = rngConstants
ElseIf rngConstants Is Nothing And Not rngFormulas Is Nothing Then
Set GetNonBlanks = rngFormulas
Else
Set GetNonBlanks = Union(rngConstants, rngFormulas)
End If
End With
End Function
--
HTH...
Jim Thomlinson
"Jim Thomlinson" wrote:
> Sorry after re-reading your post I am left wondering if you don't want
> formulas which return blank to be included??? What about Zero? Let me know...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jim Thomlinson" wrote:
>
> > What you are looking for is a bit more difficult than I think you anticipate.
> > What you need to do is to create a range that is the union of the constants
> > and formulas... Give this a try...
> >
> > Public Sub test() 'Run me
> > Dim rng As Range
> >
> > Set rng = GetNonBlanks(Sheets("Sheet1"))
> > If Not rng Is Nothing Then rng.Select
> >
> > End Sub
> >
> > Public Function GetNonBlanks(ByVal wks As Worksheet) As Range
> > Dim rngConstants As Range
> > Dim rngFormulas As Range
> >
> > With wks.Cells
> > On Error Resume Next
> > Set rngConstants = .SpecialCells(xlCellTypeConstants)
> > Set rngFormulas = .SpecialCells(xlCellTypeFormulas)
> > On Error GoTo 0
> > End With
> >
> > If rngConstants Is Nothing And rngFormulas Is Nothing Then
> > Set GetNonBlanks = Nothing
> > ElseIf Not rngConstants Is Nothing And rngFormulas Is Nothing Then
> > Set GetNonBlanks = rngConstants
> > ElseIf rngConstants Is Nothing And Not rngFormulas Is Nothing Then
> > Set GetNonBlanks = rngFormulas
> > Else
> > Set GetNonBlanks = Union(rngConstants, rngFormulas)
> > End If
> >
> > End Function
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Constantly Amazed" wrote:
> >
> > > Hi
> > >
> > > I have some code which is returning a starting point and end point for cells
> > > which contain data or formulas in a spreadsheet. I want to limit this to
> > > only cells which display data. My formulas concatenate data from an input
> > > sheet but display "" on lines in which no data has been entered on the input
> > > sheet.
> > >
> > > With ActiveSheet.UsedRange
> > > StartRow = .Cells(1).Row
> > > StartCol = .Cells(1).Column
> > > EndRow = .Cells(.Cells.Count).Row
> > > EndCol = .Cells(.Cells.Count).Column
> > > End With
> > >
> > > I believ it is the UsedRange element that needs changing but I'm unsure what
> > > it needs to be replaced with.
> > >
> > > Thanks
|
Similar Threads
