Cell range excluding blank returns

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Jim

Thank you very much. The code seems to work just fine. My formulas
concatenate data (if any exists) and therefore 0 is not returned at any point.

Once again thanks

G