remove all CALC empty rows and columns

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?

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

  1. 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. )

  2. 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. :slight_smile:

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.

  1. Would you rather iterates over all sheets (and specify sheet by name to excluded if there are any)?
  2. 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.