| View previous topic :: View next topic |
| Author |
Message |
cyphrevil
General User
Joined: 13 Apr 2012
Posts: 7
|
 Posted: Fri Jun 22, 2012 1:54 am Post subject: Macro can see the formulas and thus cells are not empty |
 |
|
Hi,
Here's the macro:
=========================================================
Sub Macro1()
Dim i As Long
'Range("A9:J2000").Select
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
End Sub
=========================================================
..unfortunately cells with no values but only with formulas in them are being treated as non-content-free and are not being removed.
Generally what I'm looking for is a quickest macro to remove all rows that are empty (not even single cell in the row has any value), any help very appreciated. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 2:39 am Post subject: |
|
|
You are using the wrong application. That code has been written to control the Excel application by Microsoft.
This forum is about OpenOffice.org (now Apache Open Office).
This subforum is about the Calc component of that software.
There is another subforum for macro related questions as far as the code is written for this office suite.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
cyphrevil
General User
Joined: 13 Apr 2012
Posts: 7
|
| Posted: Fri Jun 22, 2012 2:52 am Post subject: |
|
|
| Villeroy wrote: | You are using the wrong application. That code has been written to control the Excel application by Microsoft.
This forum is about OpenOffice.org (now Apache Open Office).
This subforum is about the Calc component of that software.
There is another subforum for macro related questions as far as the code is written for this office suite. |
S####### I am so sorry, but the question was meant for OO Calc, sorry for wrong code pasted |
|
| Back to top |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Fri Jun 22, 2012 9:29 am Post subject: |
|
|
| Code: | Sub Main
with thisComponent.sheets(0).getCellrangeByName("A9:J2000")
ec = .Columns.count-1
for i = .Rows.count-1 to 0 step -1
if .getCellRangeByPosition(0, i, ec, i).computeFunction(2) = 0 then
.Rows.removeByIndex(i,1)
end if
next
end with
End Sub |
|
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 9:51 am Post subject: |
|
|
Z1: =SUM(LEN($A1:$Y1)) [Ctrl+Shift+Enter]
This array formula calculates the sum of all lenghts in this row, left of this cell.
Copy the cell and paste down the column (drag&drop will not work in this case).
Filter by 0.
Select the whole range and delete entire rows.
Remove the filter
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
