Macro for Deleting Current Row from A to AK
Macro for Deleting Current Row from A to AK
I need the macro, in Basic, to ignore formulas and formats, but to delete everything else.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Macro for Deleting Current Row from A to AK
Something like this?
495 = 1+2+4+8+32+64+128+256 (16 - FORMULA and 512 - FORMATTED skipped)
See constants group CellFlags
Code: Select all
oSheet.getCellRangeByName("A1:AK1048576").clearContents(495)
See constants group CellFlags
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Macro for Deleting Current Row from A to AK
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.
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.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Macro for Deleting Current Row from A to AK
Forgive my bad English - what does "current row" mean?daveg7 wrote: 1) Determine the current row
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?
Answers to these questions will allow us to change string "A1:AK1048576" from my example as you needdaveg7 wrote: 2) Select only cells A-AK, but only in the current row
.clearContents(495) wiil do itdaveg7 wrote:3) Then delete everything, except for formats and formulas.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Macro for Deleting Current Row from A to AK
JohnSUN-Pensioner wrote:Forgive my bad English - what does "current row" mean?daveg7 wrote: 1) Determine the current row
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
Answers to these questions will allow us to change string "A1:AK1048576" from my example as you needdaveg7 wrote: 2) Select only cells A-AK, but only in the current row
---> 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.
.clearContents(495) wiil do itdaveg7 wrote:3) Then delete everything, except for formats and formulas.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Macro for Deleting Current Row from A to AK
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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English