I try to write my first macro, but when I execute it gives an error:

BASIC runtime error. A variable of type Object is not set.

The essence of the macro: delete all rows of the table except those in which there are cells with the string value s1, s2 or s3.

sub RemoveRow dim iLastRow as Long dim i as Integer dim s1, s2, s3 as String s1 = "Вход" s2 = "Выход" s3 = "Отказ" iLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'ищет последнюю заполненную строку For i = 4 To lLastRow If Cells(i,5).Value = Not s1 Or Cells(i,5).Value = Not s2 Or Cells(i,5).Value = Not s3 Then Rows(i).Delete Next end sub

    2 answers 2

    You write macros in LibreOffice using VBA Excel syntax. The syntax is slightly different.

    For Excel VBA:

     Sub RemoveRow() Dim iLastRow As Long Dim s1, s2, s3 As String s1 = "Вход" s2 = "Выход" s3 = "Отказ" iLastRow = Cells(Rows.Count, 1).End(xlUp).Row Do While (iLastRow >= 4) If (Cells(iLastRow, 5) <> s1) And (Cells(iLastRow, 5) <> s2) And (Cells(iLastRow, 5) <> s3) Then Rows(iLastRow).Delete End If iLastRow = iLastRow - 1 Loop End Sub

    For LibreOffice Basic:

     Sub RemoveRow Dim iLastRow As Long Dim s1, s2, s3 As String Dim s As String Dim oDoc As Object s1 = "Вход" s2 = "Выход" s3 = "Отказ" oDoc = ThisComponent Curs = oDoc.Sheets(0).createCursor Curs.gotoEndOfUsedArea(True) iLastRow = Curs.Rows.Count Do While (iLastRow >= 3) s = oDoc.Sheets(0).getCellByPosition(4, iLastRow).getString() If ((s <> s1) And (s <> s2) And (s <> s3)) Then oDoc.Sheets(0).Rows.removeByIndex(iLastRow, 1) End If iLastRow = iLastRow - 1 Loop End Sub

    The code is of course primitive, but it works. And yet, indexes in Excel start from 1, and in LibreOffice c 0.

    • I think even in the answer you can focus on the fact that in such cases you should always bypass the lines from the bottom up, not from top to bottom. - Edward Izmalkov
    • @Eduard Izmalkov, thank you true remark. Forgot to mention it. - slippyk

    I encountered all the difficulties from the top to the bottom, and came to this code:

     Sub RemoveRow() s1 = "Вход" s2 = "Выход" s3 = "Отказ" i = 4 Do While Cells(i, "E").Value <> "" If Cells(i, "E").Value <> s1 And Cells(i, "E").Value <> s2 And Cells(i, "E").Value <> s3 Then Rows(i).Delete i = i - 1 End If i = i + 1 Loop End sub

    5 times checked this macro on different tables, the result is completely satisfied. Maybe there are some pitfalls? And with macros in Libre, I realized that for now it is better not to get involved, but to study at least VBA.

    • You do not take into account the fact that among the values ​​may be an empty string (and the condition does not say that this can not be). Your code will stop at this line. - Edward Izmalkov