Excel Extra Columns After Clear All

Question

So I have a workbook that is inordinately slow. I noticed that one of the sheets has a ton of extra columns (all the way to XFD), however after I select the extra columns and delete them or clear all their contents and save, they don't go away. Is there any other way of getting rid of extra columns? Open to both VBA and GUI options.

For reference, I have a macro that copies on cell into a bunch of other cells on other sheets. On another workbook with 4 times as many sheets it takes ~7 seconds to run; on this workbook it takes 10-15min per sheet.

Edit: Also some of the cells on some of the rows become yellow after deleting the columns, but none of the macros in the workbook reference the sheet; if I disable macros the cells still fill in yellow.

Edit 2: Further testing reveals that the rows are filling in with yellow from off the sheet. If I only delete n columns, then n columns are added to the end of the sheet with the cells in some rows filled yellow.

Edit 3: Clearing all contents on the sheet (instead of just on the columns being deleted) seems to have worked, but I don't know why. Note that there is a table on the sheet and I selected no when excel asked if I wanted to delete the table, so I refreshed and it kept my content. I also don't understand where the cells with yellow were coming from, they did not show up if I deleted the cells and had them fill up, instead of deleting the columns.

Your macro surely can be speeded up by optimizing it and with no connection to the total number of rows or columns in the sheet. You may post it to have users help you in making it faster — user3598756
I don't care about the macro speed, everything is slow; copy pasting takes minutes, and the macro did lots of that. I needed to delete excess rows, which I've managed, but I want to know how they showed up so I can stop this from happening again. — Adam Martin
I'm not that deep into excel to give you the right answer for sure. So here's some guessings: code in worksheet code pane? Conditional formatting? — user3598756
Cleared conditional formatting, there is code on the worksheet, but it does not reference the sheet, and I had macros disabled for testing. — Adam Martin
The yellow color may have come from you setting formatting to an entire row (or the entire worksheet) at some point. — Chel

Unknown Unknown · Accepted Answer · 2016-04-07T21:39:51

You need to refresh the Worksheet.UsedRange property and or Range.SpecialCells method with xlCellTypeLastCell property.

with worksheets("sheet1")
    debug.print .cells.specialcells(xlCellTypeLastCell).address(0,0) '<~~ XFD99
    range("AA:XFD").clear
    debug.print .cells.specialcells(xlCellTypeLastCell).address(0,0) 'no change, still XFD99
    .usedrange
    debug.print .cells.specialcells(xlCellTypeLastCell).address(0,0) 'now Z99
end with

Saving the workbook will also refresh the xlCellTypeLastCell property.

Excel Extra Columns After Clear All

1 Answers