# [SOLVED] How Can I Delete Data with Macro [closed]

Hello!

I try to write macro in Libre Office Macro. I want to delete data by using Libre Office Macro. How can I write this code? Can you help me?

My code is here:

Sub CopyPasteworking

With xA
.Sheet       = 0
.Column      = 0
.Row         = iCount6
End With
With xA1
.Sheet       = 0
.Column      = 1
.Row         = iCount6
End With
With xA2
.Sheet       = 0
.Column      = 2
.Row         = iCount6
End With

With yA
.Sheet       = 2
.Column      = 0
.Row         = iCount6
End With
With yA1
.Sheet       = 2
.Column      = 1
.Row         = iCount6
End With
With yA2
.Sheet       = 2
.Column      = 2
.Row         = iCount6
End With

With x2a
.Sheet       = 0
.Column      = 0
.Row         = iCount6
End With
With y2a
.Sheet       = 0
.Column      = 1
.Row         = iCount6
End With

For iCount6 = 0 to 6

dim document as object
document = ThisComponent
sheet1 = document.sheets(0)
sheet2 = document.sheets(1)
sheet3 = document.sheets(2)

for iCount7 = 0 to 6
if sheet1.getCellByPosition(0,iCount6).String = sheet2.getCellByPosition(0,iCount7).String then

xA.Row = iCount6
yA.Row = iCount6
xA1.Row = iCount6
yA1.Row = iCount6
xA2.Row = iCount6
yA2.Row = iCount6

SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
PasteCell = ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column, yA.Row)
PasteCell.DataArray = SourceCell.DataArray

SourceCell = ThisComponent.Sheets(xA1.Sheet).GetCellByPosition(xA1.Column, xA1.Row)
PasteCell = ThisComponent.Sheets(yA1.Sheet).GetCellByPosition(yA1.Column, yA1.Row)
PasteCell.DataArray = SourceCell.DataArray

SourceCell = ThisComponent.Sheets(xA2.Sheet).GetCellByPosition(xA2.Column, xA2.Row)
PasteCell = ThisComponent.Sheets(yA2.Sheet).GetCellByPosition(yA2.Column, yA2.Row)
PasteCell.DataArray = SourceCell.DataArray

else

end if
Next iCount7
Next iCount6

End Sub


I try to delete data if the --> sheet1.getCellByPosition(0,iCount6).String = sheet2.getCellByPosition(0,iCount7).String

I want to delete instead of copy paste because of I want to copy the data if they are the common.

And I added my libre office calc sheets.

I hope you could help meC:\fakepath\copy paste without common data.ods

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2019-05-08 22:37:02.455510

Sort by » oldest newest most voted

Sub CopyPasteworking

document = ThisComponent
sheet1 = document.sheets(0)
sheet2 = document.sheets(1)
sheet3 = document.sheets(2)

identifiers = sheet1.getCellRangeByName("A1:A7").getDataArray()
used_entrys = sheet2.getCellRangeByName("A1:A7").getDataArray()

c = 0
for i = 0 to 6
unused = true
for n = 0 to 6
if identifiers(i)(0) = used_entrys(n)(0) then
goto label
end if
next n
if unused then

source.StartRow = i
source.EndRow = i
source.EndColumn = 2

target.Sheet = 2
target.Row = c

sheet1.copyRange(target, source)
c = c+1
end if
label:
next i

End Sub

more

@karolus You are great! Thank you. It run. Ask for improvement, It can arrange otomatically its rows and colums? For example my data can be 1000 so I need to write 1000 instead of 6 in my for loop and I should write A1:A1000 instead of A1:A7. So is there any ways to do for it? In addition, I want to add sheet in front of these sheet, but I couldn't. How can I do it?

( 2019-05-08 10:27:30 +0100 )edit

Yes, of course, change for Example:

identifiers = sheet1.getCellRangeByName("A1:A1000").getDataArray()
used_entrys = sheet2.getCellRangeByName("A1:A500").getDataArray()

c = 0
for i = 0 to 999  'accord to **identifiers** Range'
unused = true
for n = 0 to 499  'accord to **used_entrys** Range'

( 2019-05-08 11:06:56 +0100 )edit

Thanks @karolus , I got it. So I need one more question that is I want to add sheet in front of these sheet, but I couldn't. How can I do it?

( 2019-05-08 12:07:33 +0100 )edit

please dont ask that stupid! its obviously either in menu > Sheet > Insert Sheets or in the Contextmenu of the Sheettabs.

and its also obvious that you have to change the respective Indeces in the Makro in these lines:

sheet1 = document.sheets(0)  'points in any case to the very first sheet'
sheet2 = document.sheets(1)  'etcpp'
sheet3 = document.sheets(2)


if you want to avoid those sideeffects on changing your sheetstructure, you may decide for:

sheet1 = document.sheets().getByName( "ANA TABLO" )


but now you need to change the Sheetname in Makro if you rename the Sheet itself.

( 2019-05-08 15:19:09 +0100 )edit

I changed my code like you said before you said me like this:

    sheet1 = document.sheets(1)
sheet2 = document.sheets(2)
sheet3 = document.sheets(3)

target.Sheet = 3
target.Row = c


But It I couldn't run it. And I began to think is there any places to change there or not. target.Sheet was 2 before and I changed it with 3 because of I added a new sheet.

( 2019-05-08 15:29:40 +0100 )edit

Ok sorry…

youre need also to insert a additional line :

        source.Sheet = 1

( 2019-05-08 15:44:13 +0100 )edit

Thank you! I solved my problem completely

( 2019-05-08 15:59:18 +0100 )edit

I didn't study your code thoroughly. Roughly, it seems much too complicated to me. A clear description of the intentions in ordinary words might help.
Basically you can clear (make blank again / force to Type=0) a cell in a macro by assigning the empty string to either its .String or to its .Formula property. You can do the same for ranges using .setDataArray() or setFormulaArray()with arrays filled with Variant/Empty (like freshly dim'd) or with empty strings.
In addition there is the .clearContents(Flags) method callable for any SheetCellRange or SheetCellRanges object as described here. It allows for using flags to specify the ways of working more precisely.

===Edit1 2019-05-07 10:35 UTC regaring the first comment below===

Sub demo
a1 = ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 0, 0)
REM Change this according to your testing
REM Ways to clear a cell/range: Make it BLANK again without touching attributes

REM Way 1 REM Only single cell
a1.String = ""

REM Way 2 REM Only single cell
a1.Formula = ""

REM Way 3 Also applicable if a1 actually is a cell RANGE of more than one cell
Dim cleanA(a1.Columns.Count-1, a1.Rows.Count-1) REM All the elements are Variant/Empty mow.
a1.setFormulaArray(cleanA)

REM Way 4 With Flags set to 7 it will clear text, ordinary numbers, dates(recognized by format).
REM No attributes or styles
a1.clearContents(7)

REM Variant 4a Should clear everything, including attributes, styles, objects.
a1.clearContents(1023)
End Sub


===End Edit1===

more