Ask Your Question
0

BASIC+Calc: How to check blank cell quickly

asked 2019-11-16 06:04:47 +0100

lonk gravatar image

updated 2019-11-16 07:16:51 +0100

From this sheet:

image description

I did :

   'The first data row must not be blank
    i = 0
    For c = 0 To 2
       If c = 0 And oSheet.GetCellByPosition(c,1).Value = 0 Then i = i + 1
       If c = 1 And oSheet.GetCellByPosition(c,1).String = "" Then i = i + 1
       If c = 2 And oSheetl.GetCellByPosition(c,1).Value = 0 Then i = i + 1
    Next
    If i = 3 Then
       MsgBox "The first data row must not be blank !"
       Exit Sub
    End If
    'And each data row must not have a blank cell except blank row
    For r = 1 To 100
       i = 0
       For c = 0 To 2 
          If c = 0 And oSheet.GetCellByPosition(c,r).Value = 0 Then i = i + 1
          If c = 1 And oSheet.GetCellByPosition(c,r).String = "" Then i = i + 1
          If c = 2 And oSheet.GetCellByPosition(c,r).Value = 0 Then i = i + 1
       Next
       If i > 0 Then
          MsgBox "Row " & (r + 1) & "has blank cell(s) !"
          Exit Sub
       End If
    Next

Is there any short code for checking blank row or blank cell(s) in a row ?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-11-16 08:03:12 +0100

Hello @lonk

Yes, there is queryEmptyCells() method to check for empty cells in the CellRange object. Short example for your A1:C5 range:

oRange = ThisComponent.Sheets(0).getCellRangeByPosition(0,0,2,4)
oQueryResult = oRange.queryEmptyCells()
If oQueryResult.count = 0 Then
    Msgbox "There are no empty cells found"
    Else
    Msgbox "Empty cells found: " & oQueryResult.AbsoluteName
End If
edit flag offensive delete link more

Comments

Dear @SM_Riga ,

Thank you so much.

lonk gravatar imagelonk ( 2019-11-16 10:35:46 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-16 06:04:47 +0100

Seen: 17 times

Last updated: Nov 16