Macro for Deleting Current Row from A to AK

[daveg7]

I need the macro, in Basic, to ignore formulas and formats, but to delete everything else.

[JohnSUN-Pensioner]

Something like this?

Code: Select all

oSheet.getCellRangeByName("A1:AK1048576").clearContents(495)

495 = 1+2+4+8+32+64+128+256 (16 - FORMULA and 512 - FORMATTED skipped)
See constants group CellFlags

[daveg7]

I was looking to have the macro do the following:

1) Determine the current row
2) Select only cells A-AK, but only in the current row
3) Then delete everything, except for formats and formulas.

[JohnSUN-Pensioner]

1) Determine the current row

Forgive my bad English - what does "current row" mean?
A row with an active cell? The one that is always surrounded by a black border is always the only one on the sheet.
The first line of the current selection? If several lines are selected, do the others ignore?
What should we do if user have selected several non-adjacent ranges using the Ctrl key?

2) Select only cells A-AK, but only in the current row

Answers to these questions will allow us to change string "A1:AK1048576" from my example as you need

3) Then delete everything, except for formats and formulas.

.clearContents(495) wiil do it

[daveg7]

1) Determine the current row

Forgive my bad English - what does "current row" mean?
A row with an active cell?

---> Yes, it means the ONE row where the active cell is

The one that is always surrounded by a black border is always the only one on the sheet.
The first line of the current selection? If several lines are selected, do the others ignore?
What should we do if user have selected several non-adjacent ranges using the Ctrl key?

---> I hadn't thought about that, but it would be great to do the same operation on all these rows

2) Select only cells A-AK, but only in the current row

Answers to these questions will allow us to change string "A1:AK1048576" from my example as you need

---> I don't understand how this last line of code can be true, because you're selecting the rows of the entire sheet, and I need only the current row.

3) Then delete everything, except for formats and formulas.

.clearContents(495) wiil do it

[JohnSUN-Pensioner]

Well, it seems to me that this code will do something similar to your wishes:

Code: Select all

Sub ClearColumnsInRange(Optional sRangeName As String)
Dim oDoc As Variant
Dim oRows As Variant
Dim oRangeForClean As Variant
Dim oRowDescriptions As Variant
Dim aTemp As Variant
Dim nRow As Long, i  As Long
	If IsMissing(sRangeName) Then sRangeName = "A1:AK1048576"
	oDoc = ThisComponent
	oRangeForClean = oDoc.getCurrentController().getActiveSheet().getCellRangeByName(sRangeName)
	If IsNull(oRangeForClean) Or IsEmpty(oRangeForClean) Then Exit Sub 	' Wrong range address
	oRows = oRangeForClean.getSpreadsheet().getRows()
	
	oRowDescriptions = oDoc.getCurrentSelection().getRowDescriptions()
	For i = LBound(oRowDescriptions) To UBound(oRowDescriptions)
		aTemp = Split(oRowDescriptions(i), " ")
		nRow = Val(aTemp(UBound(aTemp))) - 1
		If nRow > 0 Then oRangeForClean.queryIntersection(oRows.getByIndex(nRow).getRangeAddress()).clearContents(495)
	Next i
End Sub