[Solved] Deleting Specific Columns?

[AndrewKelly]

I attempted to create a macro today to delete some columns from a spreadsheet. Recorded the macro, reloaded the sheet, and tested. I was hoping the system would record which columns I was deleting, but apparently it won't as it just deleted only the first column a number of times. How can I tell this to delete specific columns? Like A, then B, then B again, then D, etc... Or is there a way to shorten this up to one command that deletes them all at once? Total noob here.

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub


sub RemoveUnnecessary
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())


end sub

[UnklDonald418]

The macro you uploaded simply deletes the current column 21 times.

How can I tell this to delete specific columns? Like A, then B, then B again, then D, etc...

When using recorded macros you must plan and carefully record your macro.
The macro recorder records relative cursor movement so if you are always deleting the same columns then begin recording with the cursor in Column A, and always run the macro with the cursor in Column A. Since the recorder can't record mouse movements you must use arrow keys when moving to different columns.
Here is a macro I recorded macro that deletes A, then B, then B again, then D, so long as you run the macro while the cursor is in Column A.

Code: Select all

REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args6())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())


end sub

If you run the macro with the cursor in column C it will delete C then D then D again then F

[F3K Total]

Hello,
or you use this written code, define the columns to be deleted in code line 1

Code: Select all

Const SColumns = "A,C,E,F,H,K,L" 'ascending

Sub S_DELETE_COLUMNS
    aColumns = Split(SColumns,",")
    oSheet = ThisComponent.CurrentController.ActiveSheet
    for i = uBound(aColumns) to 0 step -1
        oColumn = oSheet.Columns.getbyname(aColumns(i))
        oColumn.Columns.removebyindex(0,1)
    next i
End Sub

See attached example
R

[AndrewKelly]

Thanks so much! Also thank you for including the command button in the file, now I understand a little of how to use those.

[JohnV]

You don't need a macro to do this. You can delete one or more columns at the same time.
Select the first column by clicking its header, use the Shift and/or Ctrl key as you normally would to select additional columns.
Right click it the header of any selected column and click Delete Column. All selected columns are gone.
To do a single column you just right click its header without the need to first select it.

You can do the same to delete row(s).