Last cell with data (but not space)

Jonjozz · March 31, 2023, 8:10pm

I have this spreadsheet

There is a space in cell H83
If I write the macro code

Sub CheckLastRow

Dim oCursor as Object
Dim LastCol, LastRow as Long
Dim lf as String : lf = chr(10)
Dim oDoc As Object : oDoc = ThisComponent
Dim oSheet As Object : oSheet = oDoc.Sheets.getByName(“Gratuita”)

oCursor = oSheet.createCursor
oCursor.gotoEndOfUsedArea(False)
LastRow= oCursor.RangeAddress.EndRow
LastCol = oCursor.RangeAddress.EndColumn
msgbox LastRow & lf & LastCol
End Sub

I get this message

The question is…
How do I get the last cell with data only (not space)?

Lupp · March 31, 2023, 8:47pm

Why do you attach the useless umage?
What’s the “last cell” in your understanding (not just containing spaces)?
The last cell of the kind in the last used row?
The last cell of the kind in the last used column?
Something else?
What blocks an approach which first would “trim” all used cells?

eeigor · March 31, 2023, 8:53pm

Ctrl+Home, Ctrl+Shift+End

Jonjozz · March 31, 2023, 9:06pm

The last cell used on spreadsheet…in my case it would be H76.
Thanks for replying

eeigor · March 31, 2023, 8:44pm

Try this.

Jonjozz · March 31, 2023, 9:06pm

Thanks for replying, but I need a macro code.

eeigor · March 31, 2023, 9:13pm

Call queryContentCells() and loop check each cell for a space.
Trim(oCell.String) = ""
Start at the end and move backwards to the first data cell, which will be the last.

eeigor · April 1, 2023, 2:33pm

Nevertheless, it is better to tidy up the sheet than to create macros…

Lupp · March 31, 2023, 9:17pm

Try
Code was erroneous! Withdrawn!
Hopefully correct code in my final comment below.

Sub reportLastUsedCellOfLastUsedColumnDisregardingWhitespace()
REM Erroneous code deleted!
 REM Thanks to @eeigor the correction:
 MsgBox("R" & (.EndRow + 1) & "C" & (.EndColumn + 1))
End Sub

eeigor · March 31, 2023, 9:21pm

I could be wrong, but the cell with the space in your code doesn’t have to be out of range with the actual data.

Lupp · March 31, 2023, 9:23pm

Sorry. Didn’t understand.
What do you mean by

eeigor · March 31, 2023, 9:25pm

The cell with the space can be A1. It will be the last one, which is wrong.

Jonjozz · March 31, 2023, 9:27pm

The result is

Sorry, it didn´t work

I forgot to mention…There is a heading on spreadsheet

Lupp · March 31, 2023, 9:32pm

Again: What is “the cell with the space”?
Th sub I suggested first looks for the used area. Then it creates a search descriptor and looks -using RegEx- for cells (ranges of such cells) containing data (not formulas) of which at least one character NOT belongs to the RegEx shorthand class \s (that’s whitespace). Upper case \S tells NOT here.
We need, however, an assurance that .findAll returns its findingts in the order I only know from experience, but regard as stable.

eeigor · March 31, 2023, 9:34pm

@Lupp, sorry & thanks

Jonjozz · March 31, 2023, 9:36pm

The cell H83 has space only…not data.
The cell technically is not empty …It has a string

eeigor · March 31, 2023, 9:39pm

& "C" & (.EndColumn + 1) ??

Lupp · March 31, 2023, 9:43pm

Stop attaching these silly images! Attach useful examples.
The code worked for me as expected. Merging of cells is evil. However, I can’t see how it should spoil the working of the macro in this case.

@eeigor found a real error. There must be “+” as he stated.
This can’t explain the R3 reported by the questioner.
I need the example file. Otherwise I’m stabbing in the dark.
NO MORE IMAGES, PLEASE!

Jonjozz · March 31, 2023, 10:07pm

My calc is very complex, but the main issue is…
When the macro code creates a print area, the blank cells shouldn´t be printed.
sample.ods (110.7 KB)

If you want to see my calc and its macro codes…here it is.
I´m brazilian…many parts of the macro is in portuguese.

Lupp · March 31, 2023, 11:42pm

[Of course, I don’t need all the complexity, and I won’t study the contained code.]

Obviously my assumption concerning the order of the findings was wraon - and in hindsight I have to concede that I should have known.

I now suggest a similar but substantially different approach:
To get the bottommost “\S-cell” of the right most “\S-column” of a sheet, first look for the rightmost column wit \S-cells, and then pick the bottommost \S-cell of it. This should even be more efficient.

Raw code:

Sub reportLastUsedCellOfLastUsedColumnDisregardingWhitespace()
REM Formula cells are disregarded!
sheet     = ThisComponent.CurrentController.ActiveSheet
cellCur   = sheet.createCursor()
cellCur.gotoStartOfUsedArea(False)
cellCur.gotoEndOfUsedArea(True)
lmC       = cellCur.RangeAddress.StartColumn
rmC       = cellCur.RangeAddress.EndColumn
sd        = sheet.createSearchDescriptor()
With sd
 .SearchRegularExpression = True
 .SearchString            = "\S" REM One character not being of character type WhiteSpace
End With
bmormC = -2 REM Finally printing -1 if no \S cell was found in the sheet.
bmormR = -2
For c = rmc To lmC Step -1
 c_Column  = sheet.Columns(c)
 findings = c_Column.findAll(sd)
 uF = -1
 If NOT IsNull(findings) Then
   bmormC = c
   uF = findings.Count - 1
 EndIf
 If uF>=0 Then
  bmormR = findings(uF).RangeAddress.EndRow
  Exit For REM Final result found
 EndIf
Next c
MsgBox("R" & (bmormR + 1) & "C" & (bmormC + 1))
REM "R-1C-1" meaning "No \S cell found in the sheet".
End Sub