Macro VBA - Remove empty rows and columns with total deleted in msg

[SmurofNeves]

Hi All,

First post here, I hope someone can assist.

I have my code here and just need a msg displaying the total of rows & columns deleted by my macro.

Please advise.

Regards.

Not sure how to upload the code though

Macro_Remove_Blanks MyEdit.txt

[Richard Buttrey]

You'll need to build two counter variables for the rows and columns deleted inside your loop and increment the variables each time a deletion happens. Then read the variables into your closing message box.

You can upload your workbook in much the same way as you did with your text file.

[Rick_Stanich]

Oops, wrong post

[SmurofNeves]

Hi Richard,

Thanks for your suggestion, I've edited the code, can you please verify for me and maybe advise on any improvements.

I've saved the vb as an add in to add to the end users ribbon, I've also simplified the msg's.



PS.
(Could not attache the add in file with the code, had to put in txt again sorry.)

Edited.txt

[Richard Buttrey]

Hi,

Personally I prefer to avoid loops for this sort of stuff, certainly as far as deleting rows is concerned. Loops just carry too much of a time overhead if large ranges are involved. Filtering the rows to delete is the fastest way I know. Once filtered then use code like

Please Login or Register  to view this content.

I use that a lot for getting rid of rows. As far as columns are concerned then obviously there's not the equivalent of row filtering functionality but you could use the same .SpecialCells(xlCellTypeVisible) approach to temporarily hide all the columns you want to keep, leaving those you want to delete and then just delete the entire range in one hit as with the row filtering method before unhiding all the columns.

Apart from the speed advantage the code to do this is no more than two or three lines for the row delete and probably not many more for the column delete.

Hope this gives you further thoughts.