I’m having a code problem. I’m just learning code for Cal. It works in VBA but what am I doing wrong in LO Basic. I suspect Range is the problem. I can add the spreadsheet if that helps. Please let me know.
Thank-you.
Sub ClearCells1()
Range(“B2:B11,E2:E11,H2:H11,I2:I11,J2:J11”).ClearContents
End Sub
You need create the object for multirange and put into one the individul sub-ranges.
Sub clearRanges
dim oDoc as object, oSheet as object, oRanges as object, oRange as object, p(), i%
oDoc=ThisComponent
oSheet=oDoc.Sheets(0)
oRanges=oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'multirange
p=array("B2:B11", "E2:E11", "H2:H11", "I2:I11", "J2:J11")
for i=lbound(p) to ubound(p)
oRange=oSheet.getCellRangeByName(p(i)) 'sub-range
oRanges.AddRangeAddress(oRange.RangeAddress,true) 'add sub-range to multirange
next i
oRanges.clearContents(23) 'SDK documentation: com.sun.star.sheet.CellFlags
End Sub
Whats the advantage compared with:
Sub clearRanges
doc=ThisComponent
sheet=doc.CurrentController.ActiveSheet
rangenames=array("B2:B11", "E2:E11", "H2:J11") 'merge H2 ~ J11'!!
for each name in rangenames
range=sheet.getCellRangeByName(name)
range.clearContents(23) 'SDK documentation: com.sun.star.sheet.CellFlags
next
End Sub
Hi Kamil
I just tested your code and it works perfectly. I’m going to study how you did this. Thank you very much. Wishing you and your family a very Happy Holiday.
Bob
Hi karolus
I just tested your code and it works perfectly. I’m going to study how you did this. Two different approaches but they both work. Of course less coding using yours. Thank you very much. Wishing you and your family a very Happy Holiday’s.
Bob
The usage of multirange is faster. Fill 2200 rows in columns A,B and you can test the speed with this array:
p=array("A2:B3", "A7:B8", "A12:B13", "A17:B18", "A22:B23", "A27:B28", "A32:B33", "A37:B38", "A42:B43", "A47:B48", "A52:B53", "A57:B58", "A62:B63", "A67:B68", "A72:B73", "A77:B78", "A82:B83", "A87:B88", "A92:B93", "A97:B98", "A102:B103", "A107:B108", "A112:B113", "A117:B118", "A122:B123", "A127:B128", "A132:B133", "A137:B138", "A142:B143", "A147:B148", "A152:B153", "A157:B158", "A162:B163", "A167:B168", "A172:B173", "A177:B178", "A182:B183", "A187:B188", "A192:B193", "A197:B198", "A202:B203", "A207:B208", "A212:B213", "A217:B218", "A222:B223", "A227:B228", "A232:B233", "A237:B238", "A242:B243", "A247:B248", "A252:B253", "A257:B258", "A262:B263", "A267:B268", "A272:B273", "A277:B278", "A282:B283", "A287:B288", "A292:B293", "A297:B298", "A302:B303", "A307:B308", "A312:B313", "A317:B318", "A322:B323", "A327:B328", "A332:B333", "A337:B338", "A342:B343", "A347:B348", "A352:B353", "A357:B358", "A362:B363", "A367:B368", "A372:B373", "A377:B378", "A382:B383", "A387:B388", "A392:B393", "A397:B398", "A402:B403", "A407:B408", "A412:B413", "A417:B418", "A422:B423", "A427:B428", "A432:B433", "A437:B438", "A442:B443", "A447:B448", "A452:B453", "A457:B458", "A462:B463", "A467:B468", "A472:B473", "A477:B478", "A482:B483", "A487:B488", "A492:B493", "A497:B498", "A502:B503", "A507:B508", "A512:B513", "A517:B518", "A522:B523", "A527:B528", "A532:B533", "A537:B538", "A542:B543", "A547:B548", "A552:B553", "A557:B558", "A562:B563", "A567:B568", "A572:B573", "A577:B578", "A582:B583", "A587:B588", "A592:B593", "A597:B598", "A602:B603", "A607:B608", "A612:B613", "A617:B618", "A622:B623", "A627:B628", "A632:B633", "A637:B638", "A642:B643", "A647:B648", "A652:B653", "A657:B658", "A662:B663", "A667:B668", "A672:B673", "A677:B678", "A682:B683", "A687:B688", "A692:B693", "A697:B698", "A702:B703", "A707:B708", "A712:B713", "A717:B718", "A722:B723", "A727:B728", "A732:B733", "A737:B738", "A742:B743", "A747:B748", "A752:B753", "A757:B758", "A762:B763", "A767:B768", "A772:B773", "A777:B778", "A782:B783", "A787:B788", "A792:B793", "A797:B798", "A802:B803", "A807:B808", "A812:B813", "A817:B818", "A822:B823", "A827:B828", "A832:B833", "A837:B838", "A842:B843", "A847:B848", "A852:B853", "A857:B858", "A862:B863", "A867:B868", "A872:B873", "A877:B878", "A882:B883", "A887:B888", "A892:B893", "A897:B898", "A902:B903", "A907:B908", "A912:B913", "A917:B918", "A922:B923", "A927:B928", "A932:B933", "A937:B938", "A942:B943", "A947:B948", "A952:B953", "A957:B958", "A962:B963", "A967:B968", "A972:B973", "A977:B978", "A982:B983", "A987:B988", "A992:B993", "A997:B998", "A1002:B1003", "A1007:B1008", "A1012:B1013", "A1017:B1018", "A1022:B1023", "A1027:B1028", "A1032:B1033", "A1037:B1038", "A1042:B1043", "A1047:B1048", "A1052:B1053", "A1057:B1058", "A1062:B1063", "A1067:B1068", "A1072:B1073", "A1077:B1078", "A1082:B1083", "A1087:B1088", "A1092:B1093", "A1097:B1098", "A1102:B1103", "A1107:B1108", "A1112:B1113", "A1117:B1118", "A1122:B1123", "A1127:B1128", "A1132:B1133", "A1137:B1138", "A1142:B1143", "A1147:B1148", "A1152:B1153", "A1157:B1158", "A1162:B1163", "A1167:B1168", "A1172:B1173", "A1177:B1178", "A1182:B1183", "A1187:B1188", "A1192:B1193", "A1197:B1198", "A1202:B1203", "A1207:B1208", "A1212:B1213", "A1217:B1218", "A1222:B1223", "A1227:B1228", "A1232:B1233", "A1237:B1238", "A1242:B1243", "A1247:B1248", "A1252:B1253", "A1257:B1258", "A1262:B1263", "A1267:B1268", "A1272:B1273", "A1277:B1278", "A1282:B1283", "A1287:B1288", "A1292:B1293", "A1297:B1298", "A1302:B1303", "A1307:B1308", "A1312:B1313", "A1317:B1318", "A1322:B1323", "A1327:B1328", "A1332:B1333", "A1337:B1338", "A1342:B1343", "A1347:B1348", "A1352:B1353", "A1357:B1358", "A1362:B1363", "A1367:B1368", "A1372:B1373", "A1377:B1378", "A1382:B1383", "A1387:B1388", "A1392:B1393", "A1397:B1398", "A1402:B1403", "A1407:B1408", "A1412:B1413", "A1417:B1418", "A1422:B1423", "A1427:B1428", "A1432:B1433", "A1437:B1438", "A1442:B1443", "A1447:B1448", "A1452:B1453", "A1457:B1458", "A1462:B1463", "A1467:B1468", "A1472:B1473", "A1477:B1478", "A1482:B1483", "A1487:B1488", "A1492:B1493", "A1497:B1498", "A1502:B1503", "A1507:B1508", "A1512:B1513", "A1517:B1518", "A1522:B1523", "A1527:B1528", "A1532:B1533", "A1537:B1538", "A1542:B1543", "A1547:B1548", "A1552:B1553", "A1557:B1558", "A1562:B1563", "A1567:B1568", "A1572:B1573", "A1577:B1578", "A1582:B1583", "A1587:B1588", "A1592:B1593", "A1597:B1598", "A1602:B1603", "A1607:B1608", "A1612:B1613", "A1617:B1618", "A1622:B1623", "A1627:B1628", "A1632:B1633", "A1637:B1638", "A1642:B1643", "A1647:B1648", "A1652:B1653", "A1657:B1658", "A1662:B1663", "A1667:B1668", "A1672:B1673", "A1677:B1678", "A1682:B1683", "A1687:B1688", "A1692:B1693", "A1697:B1698", "A1702:B1703", "A1707:B1708", "A1712:B1713", "A1717:B1718", "A1722:B1723", "A1727:B1728", "A1732:B1733", "A1737:B1738", "A1742:B1743", "A1747:B1748", "A1752:B1753", "A1757:B1758", "A1762:B1763", "A1767:B1768", "A1772:B1773", "A1777:B1778", "A1782:B1783", "A1787:B1788", "A1792:B1793", "A1797:B1798", "A1802:B1803", "A1807:B1808", "A1812:B1813", "A1817:B1818", "A1822:B1823", "A1827:B1828", "A1832:B1833", "A1837:B1838", "A1842:B1843", "A1847:B1848", "A1852:B1853", "A1857:B1858", "A1862:B1863", "A1867:B1868", "A1872:B1873", "A1877:B1878", "A1882:B1883", "A1887:B1888", "A1892:B1893", "A1897:B1898", "A1902:B1903", "A1907:B1908", "A1912:B1913", "A1917:B1918", "A1922:B1923", "A1927:B1928", "A1932:B1933", "A1937:B1938", "A1942:B1943", "A1947:B1948", "A1952:B1953", "A1957:B1958", "A1962:B1963", "A1967:B1968", "A1972:B1973", "A1977:B1978", "A1982:B1983", "A1987:B1988", "A1992:B1993", "A1997:B1998", "A2002:B2003", "A2007:B2008", "A2012:B2013", "A2017:B2018", "A2022:B2023", "A2027:B2028", "A2032:B2033", "A2037:B2038", "A2042:B2043", "A2047:B2048", "A2052:B2053", "A2057:B2058", "A2062:B2063", "A2067:B2068", "A2072:B2073", "A2077:B2078", "A2082:B2083", "A2087:B2088", "A2092:B2093", "A2097:B2098", "A2102:B2103", "A2107:B2108", "A2112:B2113", "A2117:B2118", "A2122:B2123", "A2127:B2128", "A2132:B2133", "A2137:B2138", "A2142:B2143", "A2147:B2148", "A2152:B2153", "A2157:B2158", "A2162:B2163", "A2167:B2168", "A2172:B2173", "A2177:B2178", "A2182:B2183", "A2187:B2188", "A2192:B2193", "A2197:B2198")
Maybe … but it was 3 different ranges not 440
in Case I’m forced to operate on a structure like your p, I will restart on a better sheetdesign.
yes it is! I’ve tested both methods with p…array in python and @KamilLanda 's method runs 10 to 15times faster
Hi Kami
Sorry for the delay in getting back to you. I just returned from visiting my parents. In reply to your post you’re right about the code being faster and I will be adding more ranges so speed is good. Thanks so much for your help. If you have any spare time I just posted another request for more help. The title is " Help with converting VBA code to Calc code". Thank you again. Bob