how to detect a cell range if it is empty.(Macro)

[topher]

can anyone give me a sample code on how to detect if a cell range is empty. in macro

[pitonyak]

I do not have time for a complete answer, but I noticed yesterday that no answer had been given so I took the time to look this up before I leave (I have to hang some things at church).

if you can find a copy of my published book, go to page 338 Listing 13, NonEmptyCellsInRange(). The basic idea is as follows:

* Obtain the CellRange that interests you from the sheet
* Use the queryContentCells(flags) method of the cell range object.

The trick with this is that this allows you to find the cells that are NOT empty. You, on the other hand, want to know if the entire range is empty. If the query returns any cells that contain something, then the range is not empty.

I hope that this at least gets you started. I just did not want this to be lost even if I lack the time at the moment.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php

[SergeM]

here is a OOoBasic example :

[pitonyak]

This is the solution that I would use!

[topher]

[topher]

pitonyak wrote:
This is the solution that I would use!
Code:
Option Explicit

Sub TestEmpty
Dim oSheet
Dim oRange

oSheet = ThisComponent.Sheets(0)
oRange = oSheet.getCellRangeByName("A1:F8")

If IsCellRangeEmpty(oRange) Then
Print "The cell range is empty"
Else
Print "The cell range is NOT empty"
End If
End Sub

Function IsCellRangeEmpty(oRange) As Boolean
Dim oRanges 'Ranges returned after querying for the cells
Dim oAddrs() 'Array of CellRangeAddress

oRanges = oRange.queryContentCells(_
com.sun.star.sheet.CellFlags.VALUE OR _
com.sun.star.sheet.CellFlags.DATETIME OR _
com.sun.star.sheet.CellFlags.STRING OR _
com.sun.star.sheet.CellFlags.FORMULA)
oAddrs() = oRanges.getRangeAddresses()

IsCellRangeEmpty = UBound(oAddrs()) < 0
End Function



Helo,

thanks for the code that you gave me before.
it worked with cell ranges that contains no strings, numbers, formulas and etc.
but it did not worked for ranges with formulas.
I want to use it with cellranges with formulas to detect if it contains no numbers or values.

I tried the combinations of using AND instead of OR and removing some of com.sun.star.... but to no avail i failed.

hope u give me an answer.
Tnx

[pitonyak]

The code works for me as is. I entered a single formula into the range and it indicated that the range was not empty... How does this fail for you?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php

[topher]

helo,

here are the conditions that it failed::
1. the cellranges all contains formulas.
2. the cellranges will show a value or nothing due to the formulas.

the problem is, the macro says not empty even if the cell ranges shows no value.
it should say empty even it has formula since there is no value.

[pitonyak]

The query functions are not used to notice that no value is displayed, they are used to notice that a formula is in the cell. What you really want to notice is that a cell is displaying nothing, and that is a different question.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php