Macro to hide empty columns within Calc data

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
WardXmodem
Posts: 16
Joined: Fri Jan 06, 2017 3:35 am

Macro to hide empty columns within Calc data

Post by WardXmodem »

!!!!! someone marked this "solved" with an answer that has little to do with my question. I could care less about empty columns to the right of my spreadsheet.

I just exported my Google Contacts from GMAIL, and it came in as a "CK" x 190 .csv file - so 63 columns by 190 rows.

I started selecting columns to hide, but really only wanted to hide the EMPTY ones. With 63 columns, and having to ctrl-down-arrow to see if there is any data, this was not suitable. I'm the biggest macro fan around, (In general) and would love a macro to "hide empty columns", THEN I could hide ones manually -- such as "Photo" which isn't very useful as you have to click the link.

Thanks a bunch!!

07/29/21 Edited Subject from "Macro to hide empty columns in calc" to "Macro to hide empty columns within Calc data"
Last edited by WardXmodem on Thu Jul 29, 2021 8:57 pm, edited 2 times in total.
Ward Christensen, Inventor of Xmodem & BBSs
Open Office v 4.2.1, Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [solved] Hide all the empty columns in OOo Calc

Post by Villeroy »

Ctrl+End jumps to the bottom-right cell of the sheet's used area. All columns right of this cell are empty. All rows below this cell are empty.
Go one cell to the right and then Ctrl+Shift+RightArrow. Now you have selected a cell range that includes all empty columns right of the used area. Hide them.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: Macro to hide empty columns in Calc

Post by Mr.Dandy »

Use the right tool like Notepad++
OpenOffice 4.1.12 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to hide empty columns in Calc

Post by Villeroy »

Mr.Dandy wrote:Use the right tool like Notepad++
+1

Csv is a database exchange format in plain text. It is designed to mimic the structure of database tables.
Anyhow, people won't stop importing text into arithmetic calculators. You can perform the above keyboard operations while the macro recorder is running. The resulting macro should work with any spreadsheet regardless of import format or column count. But how do you remember all the macros doing some trivial keyboard operations? It is easier to remember the keyboard operations. They are roughly the same in all desktop programs.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
WardXmodem
Posts: 16
Joined: Fri Jan 06, 2017 3:35 am

Re: [solved] Hide all the empty columns in OOo Calc

Post by WardXmodem »

Villeroy wrote:Ctrl+End jumps to the bottom-right cell of the sheet's used area. All columns right of this cell are empty. All rows below this cell are empty.
Go one cell to the right and then Ctrl+Shift+RightArrow. Now you have selected a cell range that includes all empty columns right of the used area. Hide them.
:( Not relevant to my question.
Ward Christensen, Inventor of Xmodem & BBSs
Open Office v 4.2.1, Windows 10
WardXmodem
Posts: 16
Joined: Fri Jan 06, 2017 3:35 am

Re: Macro to hide empty columns in Calc

Post by WardXmodem »

Mr.Dandy wrote:Use the right tool like Notepad++
Do you mean notepad++ has a sufficient macro language to remove blank "columns" from a .csv file?

I'm in OO, wanting to learn its macro language, not another macro language.

But it is an interesting thought - I can write a macro in Mansfield Software's Kedit -- which I use as my Windows shell -- and in pseudocode:

Code: Select all

for column = "a" to "ck"
  nonblank = 0
    for row = 2 to 190 /* skip column labels */
     if isblank(row,column) = 'F' then do
        nonblank = 1
        leave
    endif
   next row
if nonblank = 0 then ... 
well, you get the idea.
Ward Christensen, Inventor of Xmodem & BBSs
Open Office v 4.2.1, Windows 10
Post Reply