Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell range excluding blank returns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell range excluding blank returns
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell range excluding blank returns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell range excluding blank returns
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell range excluding blank returns
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 "Jim Thomlinson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP returns 0 (zero) when lookup cell is blank | Excel Discussion (Misc queries) | |||
VLOOKUP returns 0 (zero) when lookup cell is blank | Excel Discussion (Misc queries) | |||
counting unique items within categories - but EXCLUDING blank cell | Excel Worksheet Functions | |||
formula for a cell which is blank returns populated cell | Excel Discussion (Misc queries) | |||
Cell returns blank result... | Excel Worksheet Functions |