Macro to delete all rows from a specific one

[Zlotz]

Hi all

I would like a macro to keep only the header (which is on the first row) of my Calc sheet.

During my search I’ve found codes to delete rows in a given range but not all the rows fro a specific one.

[JohnSUN]

It can be something like this

Sub clearOther
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim aAddr As New com.sun.star.table.CellRangeAddress
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	aAddr = oCursor.getRangeAddress()
	If aAddr.EndRow = 0 Then Exit Sub 
	oActiveSheet.getCellRangeByPosition(0, 1, aAddr.EndColumn, aAddr.EndRow).clearContents(1023)
End Sub

[Zlotz]

It works. Thanks.

[Lupp]

An alternative:

Sub clearExceptFirstRow(Optional pSheet)
If IsMissing(pSheet) Then pSheet = ThisComponent.CurrentController.ActiveSheet
rgA = pSheet.RangeAddress
rgA.StartRow = 1
pSheet.removeRange(rgA, 1)
End Sub

[Zlotz]

RangeAddress causes an error: “Object variable is not set”

[JohnSUN]

Just add line Dim rgA between Sub and If IsMissing

[Lupp]

Sorry. Didn’t consider that the Sub might get inserted into a module with Option Explicit set.
(My personal style may be a bit sloppy as compared to what @JohnSUN is ised to code.)
Additional hint: The “alternative” isn’t equivalent.
If other sheets contain formulas referencing anything in the sheet where deletion was done, the effect will be very different from what the original solution by @JohnSUN does.

[JohnSUN]

For this question, this is not essential - judging by the other question, the OP deals with CSV files, there are no formulas and other sheets (As for the coding style - I’m not accidentally, it turns out by itself )