[SOLVED] How Can I Delete Data with Macro

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

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")
xA1 = CreateUnoStruct("com.sun.star.table.CellAddress")
xA2 = CreateUnoStruct("com.sun.star.table.CellAddress")
yA1 = CreateUnoStruct("com.sun.star.table.CellAddress")
yA2 = CreateUnoStruct("com.sun.star.table.CellAddress")
x2a = CreateUnoStruct("com.sun.star.table.CellAddress") 
y2a = CreateUnoStruct("com.sun.star.table.CellAddress") 
	
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 mecopy paste without common data.ods

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===

Hi @lupp Thanks for your reply. Can you write me a simple clearContents code. I researched about it in a detail way but I couldn’t understand what I will implement it in my code. For example I will delete PasteCell but its cell to be described as a (x,y) so how will I write it? Can you help me about it? Thanks a lot

Maybe youre about something like:

Sub CopyPasteworking



	range_address = CreateUnoStruct("com.sun.star.table.CellRangeAddress") 
	
	cell_address = CreateUnoStruct("com.sun.star.table.CellAddress")
	
	
	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 = range_address
			source.StartRow = i
			source.EndRow = i
			source.EndColumn = 2
			
			target = cell_address
			target.Sheet = 2
			target.Row = c
			
			sheet1.copyRange(target, source)
			c = c+1
		end if
        label:
	next i	

End Sub

@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?

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'

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?

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.

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 = cell_address
    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.

Ok sorry…

youre need also to insert a additional line :

        source.Sheet = 1

Thank you! I solved my problem completely