Putexcel Delete or Hide Rows?

[Alex Lampert]

Hi all,

Is there a way to delete or hide excel rows using putexcel? It would be extremely useful.

Thanks!

[Chris Vecchio]

I don't know of a way to delete or hide rows entirely using putexcel. However, you can hide the values in a row by using Excel's ";;;" format in the nformat option of putexcel. This will not remove or hide the row but just suppress the display of the cell values. (See this link from Microsoft about the ";;;" format.)

(Side note: I initially tried using Mata's xl() class to hide the values. I thought the set_format_hidden() option would do this, but I couldn't get it to hide the values.)

* Create 3 rows of test values
putexcel set test.xlsx, sheet("Test") replace
putexcel A1:F1 = "Testing 1"
putexcel A2:F2 = "Testing 2"
putexcel A3:F3 = "Testing 3"

* Hide first row values using ";;;" Excel format
putexcel A1:F1, nformat(";;;")

* Use Mata to hide values in second row (
mata: b = xl()
mata: b.load_book("test.xlsx")
mata: b.set_sheet("Test")
mata: rows = (2,2)
mata: cols = (1,6)
mata: b.set_format_hidden(rows, cols, "on")

[Alex Lampert]

Thanks for the response, Chris. I have a weird situation for which I need to delete the entire rows. I essentially have a large table of varying height, and below it I have smaller tables that are always the same height. It would be nice to be able to delete the extra space between the large table and small tables. My current work around is to delete the rows manually.

Best,
Alex

[eric_a_booth]

Import the excel file (-import excel-) drop if the rows/observations are missing (you can search for missing cells by variable or use the SSC program -dropmiss- to quickly drop entire missing rows/observations) then re-export the file to xlsx (-export excel-). You can do this in a loop for all files/worksheets/etc.