How can I get this macro to work with libreoffice calc?

Hello Experts.

I have the following macro that works with Excel, but I would like to have a version that works with libreOffice as well.  I get an error with "dim r as Range" when I try to run it in libreOffice Calc.  It says "Basic syntax error.  Unknown data type Range".  Here's the macro:

Sub RemoveSelectColumns()
    Dim iColumn As Integer
    Dim strCell As String
    Dim strColumnName As String
    Dim r As Range
    
    iColumn = 1
    Do
        strCell = Cells(1, iColumn).Value
        If strCell = "" Then
            Exit Do
        End If
        If UCase(strCell) = "MOE" Then
            ' case-independent
            strColumnName = Split(Cells(1, iColumn).Address, "$")(1)
            Columns(strColumnName & ":" & strColumnName).Delete Shift:=xlToLeft
        Else
            iColumn = iColumn + 1
        End If
    Loop
End Sub

Open in new window


Any help would be greatly appreciated.
ferguson_jeraldAsked:
Who is Participating?
 
ltlbearand3Connect With a Mentor Commented:
The LibreOffice/OpenOffice objects are different and need to be handled differently.  Try this code as I think it does what the Excel Macro did.  If not, let us know:

Sub RemoveSelectColumns()
    Dim iColumn As Integer
    Dim strCell As String
    Dim strColumnName As String
    Dim objSheet as object
    
    ' This Gets the First WorkSheet
    objSheet = ThisComponent.getSheets().getByIndex(0)

    ' Set to First Column
    iColumn = 0
    Do
        strCell = objSheet.getCellbyPosition(iColumn, 0).String
        If strCell = "" Then
            Exit Do
        End If
        If UCase(strCell) = "MOE" Then
        	' Remove the current Column
		objSheet.getColumns().removeByIndex(iColumn, 1) 
        Else
            iColumn = iColumn + 1
        End If
    Loop
End Sub

Open in new window


-Bear
0
 
ragnarok89Commented:
I think you use

oSheets = ThisComponent.getSheets()
r
= oSheet.getCellRangeByName("A2:C5")

for example.
0
 
TommySzalapskiCommented:
Delete line 5. You never use r anyway.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.