[Solved] Delete row(s) based on value of first cell in row

[klocksbass]

Hello to you all.
I am trying to write a macro for a button in Calc that will delete any row(s) from row 3 through 79 in a sheet that have the first cell in the row with a value of greater than 1. Here's what I've got so far but I'm running into trouble and I am not as familiar with writing Macros for OpenOffice Calc as I would like to be. I could use a fresh set of eyes. Thank you for any help.

Code: Select all

Sub DeleteRowswithSpecificValue()
Dim Range1 As object
Dim Cell
Dim LastRow As Long

    Set Range1 = Range("A3")
    LastRow = Range1.CurrentRegion.Rows.Count
    LastRow = Range("A79").End(xlUp).Row
    Set Range1 = Range(Range1, Range1.Offset(LastRow, 0))
    For Each Cell In Range1
        If Cell.Value = "2" Then
            Debug.Print "' delete row from at address :: " & Cell.Address
            Range(Cell.Address).EntireRow.Delete
        End If
    Next
End Sub

[Villeroy]

Apply a filter, delete entire rows, remove filter.

[Matareuz]

The answer of Villeroy is right if you're okay with make work manually, but there are people, like me, who like the things simple and easy, as less work, better, in that line of thought, I would like much more just press a button upon make use of the filer.

This code isn't pretty, but works.

Code: Select all

Sub DeleteRowswithSpecificValue()
Dim mMatrix() 
Dim oActiveSheet As Object
Dim c As Integer
Dim Address As New com.sun.star.table.CellRangeAddress

oActiveSheet = ThisComponent.getCurrentController.getActiveSheet
mMatrix = oActiveSheet.getCellRangeByName("A3:A79").getDataArray
Address = oActiveSheet.getCellRangeByName("A3").getRangeAddress

	For c = UBound(mMatrix) To 0 Step -1
		If mMatrix (c)(0) > 1 Then
		Address.StartRow = Address.StartRow + c
		Address.EndRow = Address.EndRow  + c
		oActiveSheet.RemoveRange ( Address , 3)
		MsgBox "delete row " & Address.StartRow
		Address.StartRow = Address.StartRow - c
		Address.EndRow = Address.EndRow  - c
		End If
	Next
	
End Sub

[Villeroy]

Next time it is just another sheet, another range, other criteria, another action. Then you have to find the best matching macro, create a copy of your document for testing and test the edited version of that macro against the test document until you are 100% sure that your mass editing code really does the right thing.

If you know how to use a spreadsheet program (Excel, Calc, Gnumeric, Numbers, Google, whatever), you simply do what needs to be done step by step in a visual manner.

[klocksbass]

Thank you Villeroy & Matareuz for your replies. I should have mentioned that the use of filters doesn’t work in this particular situation as the spreadsheet will be used by a co-worker that barely knows how to use a spreadsheet and would be confused by the many steps needed to reach the desired goal. The code submitted by Matareuz works perfectly. There is one additional issue I would like some help on that may require me to rethink how I am going about things for the end results I’m looking for.

The Spreadsheet in question has two sheets. Sheet 1 has all the data listed in rows per transaction. So, all data from a transaction is in Row 3, for instance. The second transaction data resides in Row 4 & so on. The second sheet shows the data in a desired format with the use of merged cells and so forth.

When a transaction is PAID in full the first cell of that row is given the number 2. Other wise it remains the number 1 or is blank.

When Paid transactions are needing to be removed then Matareuz’s code works perfectly, however the cells with formulas originally referencing the now deleted row(s) formulas are now showing #REF! errors.

For example. Sheet 2 has a cell with the following code. =IF('Sheet 1'.$B$8="";"";'Sheet 1'.$B$8)
Sheet 1 row 8 has a transaction with data throughout the row. Once the row is deleted however the code in Sheet 2 will changed to the following. =IF('Sheet 1'.$B$#REF!="";"";'Sheet 1'.$B$#REF!) because the cell was looking for data in a particular row that no longer exists because that row was deleted.

What I trying to do is have the data from the next row down to move up to the row that was deleted, which it does. But for Sheet 2 to continue to look for data in the original row. In this case row 8.

I hope this makes sense. Thanks for your input and greatly appreciated help and suggestions.

[Villeroy]

Like 90% of all spreadsheet users you try to use a spreadsheet as poor database surrogate. Instead of developing a relational database with some user friendly database form(s), you try to throw macro code against an arithmetic application hoping that you can teach it a concept of "tables", "records", "fields" and "relations" (all database terms). This is a perfect waste of time and effort. If your user knows the bare minimum about using auto-filter buttons and if you re-assign the Ctrl+A shortcut to "Select current region", you would save a lot of time and effort.
With 2 "tables" and "transactions" of any kind, you can waste months, may be years, with the wrong application.

No, I can not point you to a working spreadsheet template because I have never seen one.

[MrProgrammer]

What I trying to do is have the data from the next row down to move up to the row that was deleted, which it does. But for Sheet 2 to continue to look for data in the original row. In this case row 8.

You have a macro to make adjustments to the first sheet. You can certainly add programming to the macro to make adjustments to the second sheet. For example, it could copy formula =IF('Sheet 1'.$B$8="";"";'Sheet 1'.$B$8) from the sheet to a variable, remove the row from the first sheet, and then replace the #REF! formula with the one stored in the variable. This may not be appropriate for your situation, and I can't help you with macro writing; I just wanted to suggest using your macro to fix any problems in the second sheet which it creates by changing the first sheet..

Villeroy is correct: you should be using a database for an application where the people using it are not familiar with spreadsheets. It will be more robust.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Villeroy]

https://www.mediafire.com/file/0f7ls3bi ... l.odb/file is another modification of my most frequently used demo database.
Download the document, open the downloaded copy and open the embedded form document therein.
The form shows a table grid (green) and a section where you can enter filter criteria (yellow) to filter the table grid. Empty criteria fields are ignored. On the right side you see some calculation results. When you push the OK button, both the grid and the calculation are updated based on the filter criteria.
I added a Boolean (Yes/No) field represented by check boxes in column "Check". Check some of them and hit the OK button. The checked entries will disappear. They won't be deleted. In a database it makes no difference if you store thousands or millions of records, therefore it makes no sense to delete outdated data. Preferably, they are just hidden from the user interface, so you may analyse these data later. Nevertheless, it would be very easy to add a delete button and bind it to a very simple macro.
All this happens without macro code simply because the whole stack of software is made for this kind of task.

Regarding your second question about deleting (or preferably hiding) related records in other tables, this would trivial to set up with a database.

[klocksbass]

Thanks to you all for your valuable input and help.
I was able to correct the issue of the second sheet by changing the formula to include the INDIRECT function in the argument. Indeed the use of a database would be the better choice, however I was tasked to enhance the spreadsheet if possible. If not, no biggie. But with all your help I was able to complete the task. This forum is AWESOME!
Once again thanks to you all.
klocksbass

[Villeroy]

Why "enhance" a spreadsheet if you have all the software at hand to build a simple database from scratch? The type of end user you are describing will never be able to handle a spreadsheet without accident. It simply makes no sense to let untrained users store real world data on sheets. Spreadsheets are perfect for what-if-scenarios or when you need to arrange data sets from various sources either for ad-hoc analysis or to prepare data to be stored in a database. Spreadsheets have no structure. Every single cell is a piece of software which takes any number, text or a formula together with formatting, validation and what not. This makes them versatile and difficult to handle.