Duplicate Entire Rows or Values Based on Cell Value in LibreOffice Calc using a Macro

This macro will duplicate values in column A & B according to the value specified in column C. The results will be generated in columns D & E. The first row is the header. The macro is assigned to a button (named Push in this case). This macro works only in LibreOffice Calc and uses LibreOffice Basic

Sample Data before running the Macro

The Macro:

Sub DuplicateRowsAsPerValue()

Dim i As Double
Dim j As Double
Dim z As Double
Dim w As Double

sheet = ThisComponent.getSheets().getByIndex(0)


For i = 0 To 10000

j = sheet.getCellByPosition(2, i).Value

For z = 1 To j + 1

sheet.getCellByPosition(3, z + w).String = sheet.getCellByPosition(0, i).String
sheet.getCellByPosition(4, z + w).String = sheet.getCellByPosition(1, i).String

Next z
w = w + z - 2

Next i

End Sub
 
 
The Macro (Adapted to run on LibreOffice Calc)

 

 

Assign the Macro to a Button (in this case the the green one named Push)


 

 

Duplicated values in columns D & E as per values in column C

Tested on:

 LibreOffice

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 8; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u7
Calc: threaded

Excel Macro from here: https://techcommunity.microsoft.com/discussions/excelgeneral/formula-to-duplicate-entire-rows-based-on-cell-value/3269120

Comments

Popular posts from this blog

Some lesser-known truths about programming