Ask Your Question
0

How to delete specific columns with a Macro?

asked 2015-09-24 19:56:56 +0200

Jérôme Lacroix gravatar image

I have to work with spreadsheet everyday that I export from an another system, and my first operation is always to delete the columns I don't need (for instance: A, B, E, G, H, I, J, N).

Is there a way to save or script a Macro to execute that operation with a single keystroke? This way, I wouldn't need to select every column and delete them manually.

Thanks for you help!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-02-25 13:07:30 +0200

Lion gravatar image

By John

Sub DelColumn
    Dim oSheets As Variant
    Dim oSheet As Variant
    Dim oColumns As Variant

            oSheets = ThisComponent.getSheets()     ' All sheets of this workbook
            oSheet = oSheets.getByName("Sheet1")    ' One sheet with name "Sheet1" (for example)
            oColumns = oSheet.getColumns()  ' All columns of this sheet as object (with some metods)

            oColumns.removeByIndex(1, 3)    ' Remove 3 columns from B (B-C-D)
            oColumns.removeByIndex(6, 1)    ' Remove one column (now it is column G - 6th)
    End Sub

Thnks John

edit flag offensive delete link more

Comments

I don't think this would work as written. Once you delete the first three columns the index numbers get changed. Column G won't be number 6 any longer, but it will become number 3 instead. That being said, instead of doing some manual computation that would become complicated if you need to delete several non-contiguous groups of columns, an easier way would be to reverse the order in which the columns are deleted, starting with the columns with the highest number all the way to the lowest ones.

b3ll0v1c gravatar imageb3ll0v1c ( 2017-02-28 05:22:08 +0200 )edit
0

answered 2015-10-05 04:14:26 +0200

Alex Kemp gravatar image
edit flag offensive delete link more

Comments

same situation here can you pls tell me how to remove particular columns from sheet through macro code

Lion gravatar imageLion ( 2016-02-25 12:26:34 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2015-09-24 19:56:56 +0200

Seen: 2,035 times

Last updated: Feb 25 '16