Comments on: Deleting All Types of Contents from Calc Range using Macro

By: Arindam

Arindam — Sun, 29 Aug 2021 04:26:08 +0000

In reply to Paul. Yes. You can just simply replace the range in the above code

By: Paul

Paul — Sat, 28 Aug 2021 18:22:39 +0000

Hi, I just found this macro and it solved my problem, (kind of) but I have to clear contents of non-consecutive cell ranges, like: B7:D99 and F7:G99. Ist there a way to accomplish this?

By: sergio trajano

sergio trajano — Mon, 10 Aug 2020 16:43:43 +0000

Hello Arindam.

Thank you for those posts in openoffice basic. They help us a lot.

Would you know how to find the last row with data in a especific column? The code I have found the last row in the whole sheet, not a specific column…

function f_lfim(byval sheet_name as string) as long

dim osheet as object
dim ocellcursor as object, ocellrangeaddress as object, ocell as object
dim lrownumber as long, lendrow as long

osheet = thiscomponent.getsheets.getbyname(sheet_name)
ocellcursor = osheet.createcursor()

‘ find the last used row ‘

ocellcursor.gotoendofusedarea(true)
ocellrangeaddress = ocellcursor.getrangeaddress()
lendrow = ocellrangeaddress.endrow
f_lfim = lendrow

end function

By: Expert Learner

Expert Learner — Thu, 21 Sep 2017 15:08:00 +0000

What about delete the range? I need to delete whole columns and rows.

By: Arindam Giri

Arindam Giri — Tue, 07 Apr 2015 01:31:00 +0000

In reply to Jim Smith. @disqus_xo2FJypWGT:disqus - Thanks for your input. I am glad my article helped you to solve problem. Flag 7 was not in my list, I will add as note. Do follow Debugpoint.com for more tutorials.

By: Jim Smith

Jim Smith — Tue, 07 Apr 2015 00:02:00 +0000

Thanks for the push in the right direction. I took your info along with some helpful hints in some forums and came up with this snippet of code to use. I do the programming for my wife’s gradebook for her classes. It has always been a time consuming task to copy the worksheet to a new grading period and then clear all the existing grades. The sheets are all contiguous and the ranges are the same for every sheet, so this is what I did!

****** code follows I run this with a HUGE RED button that warns that there is no built in undo. BACKUP first.

sub delete_grades ‘ subroutine to delete all grades on the active sheet

Dim oDoc As Object
Dim oSheet As Object ‘ I want SFPW through HabitsNumeric aka sheets 4-10
Dim oRange As Object
Dim i As Integer

oDoc = ThisComponent
For i = 4 to 10 ‘ sheet 4 which is human 5 since the number starts at 0 for sheet number’
oSheet = oDoc.Sheets(i) ‘ this loop iterates through all seven sheets and clears the range.
oRange = oSheet.getCellRangeByName(“D1:BU26”)
oRange.clearContents(7) ‘ the ranges never have anything but strings, datetime, or numbers, so the
next i ‘ 7 flag handles all of those and leaves all my formatting and styles intact.
MsgBox(“Finished”) ‘ this completes in mere seconds even when full of grades.
Done:
::
end sub ‘delete_grades

Hope someone can use it.