macro to delete empty cells

Mar 7th, 2002, 10:27 AM

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!

Mar 7th, 2002, 11:29 AM

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

Mar 7th, 2002, 11:40 AM

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

Mar 7th, 2002, 11:45 AM

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 · Mar 7th, 2002, 04:53 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 10:27 AM	#1
Guest Posts: n/a	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!

Mar 7th, 2002, 11:29 AM	#2
Guest Posts: n/a	Any ideas? If I haven't explained it well enough, I can give it another try, just let me know!

Mar 7th, 2002, 11:40 AM	#3
Guest Posts: n/a	Any ideas? If I haven't explained it well enough, I can give it another try, just let me know!

Mar 7th, 2002, 11:45 AM	#4
Guest Posts: n/a	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...)

Mar 7th, 2002, 04:53 PM	#5
Escalus Join Date: Feb 2002 Posts: 12	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.