remove all CALC empty rows and columns, in multiple sheet workbook, with one command to process all sheets in a workbook, like ASAP Utilities does on the PC version of Excel?
1 Like
You can’t remove cells and/or columns from a sheet. Each sheet has 1024 columns and 1048576 rows (and I doubt that you can do that on Excel). You may hide empty rows/columns, but you cant remove 'em.
Hello,
to manually hide rows, you just have to
-
Select the rows, by clicking on the rows numbers you want to hide and hold down SHIFT.
(If the Rows are continuous you can click the first and last row. ) -
In the top menu, now goto/click on
Format>Rows>Hide(you can do the same with the right click)
Now if you want to automate the hidding of the same rows in multiple sheets you can use this little macro subroutine. Just edit the sheet names and ranges to your need and place it into tools > macros > edit macros.
Sub HideRows
set names=array("Sheet1","Sheet2","Sheet3")
set ranges=array("A1:A3","A5:A10")
for i = lbound(names) to ubound(names)
sheet = ThisComponent.getSheets().getByName(names(i))
for j = lbound(ranges) to ubound(ranges)
range = sheet.getCellRangeByName(ranges(j))
range.Rows.isVisible = False
next
next
end sub
If you have further questions, just ask in the comments.
Hope that helps.
Here a version, where you only specify the sheets to skip (skips) and the ranges to hide (hides)
Sub HideRows2
set skips = array("Sheet1","Sheet33")
set hides = array("A1:A3","A5:A10")
set sheets = ThisComponent.getSheets()
doskip = false
for i = 0 to sheets.getCount() - 1
Set sheet = sheets(i)
doskip = false
for j = lbound(skips) to ubound(skips)
skip = skips(j)
if(sheet.Name = skip) then
doskip = true
exit for
endif
next
if (doskip <> true) then
for j = lbound(hides) to ubound(hides)
hide = hides(j)
range = sheet.getCellRangeByName(hide)
range.Rows.isVisible = not range.Rows.isVisible rem reverse/toggles visibility
next
endif
next
end sub
This should be more convinient if you have a large number of sheets, which are similar in strukture.
One could enhance this Version even further by adding conditions but … since that was not asked for i’ll leave it a as an exercise for interessted people. 
Thanks for the subroutine macro idea - I have a 1.5mb Workbook with 255 sheets all with different names; is there an easy way to edit the macro without adding the actual sheet names?
FYI if you’re on a PC you can easily do this with ASAP Utilities Add-On, but I’m on macOS.
- Would you rather iterates over all sheets (and specify sheet by name to excluded if there are any)?
- Are the rows you want to hide always the same?
@ilight4u
i updated my answer so it might be more useful for you.
Hope that helps.
**30 minutes later I’ve realised it was hiding rows, and it hid ones I didn’t want it to (containing “.”)
anyway, still learning
Hi I know this is 2 years old at this point but it was the first result when I searched how to do it,
here’s my solution based on the info here:
Select the column of data, apply a standard filter as below, I added a period “.” to the contains field to ensure it would count as a wildcard *
removed all the empty cells from the data, hopefully this helps someone 