macro to delete empty cells

[Guest]

Hi Guys!

Here's the deal:

I have this template invoice, and I am reserving 1,000 or so cells in the "Item" column (A1), because I know that every month when I create the invoice for that period, this is the maximum number of items I will need to list. The thing is, sometimes I may only have 300, 400, etc., and I don't want to have all the extra empty cells there. In other words, if let's say I have 550 items for the month, (A1:A550 contain text). I want excel to recognize this and "autofit" by deleting the entire row range of A551-A1000. I would like two things- a macro to achieve this purpose, and then also another marco to restore the sheet, back to the original 1,000 lines, without any text. Any ideas? Thanks!

 

[Guest]

Any ideas? If I haven't explained it well enough, I can give it another try, just let me know!

 

[Guest]

Any ideas? If I haven't explained it well enough, I can give it another try, just let me know!

 

[Guest]

Perhaps if I write in English how i think the code for the macro will look, it might be easier:

For the range A1:A1000, delete all rows where the cell is empty

(i.e.- if A500 thu A1000 are empty, these rows will be deleted...)

 

[Escalus]

Assuming that when an item is entered there will always be data in column A :-
[A1:A1000].SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Post again if the assumption is wrong.

To clear all the cells :-
[A1:A1000].ClearContents

To display all the rows :-
[A1:A1000].EntireRow.Hidden = False

Note : You could have created code to do what you want with the macro recorder.