Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: macro to delete empty cells

  1. #1
    Guest

    Default

    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!

  2. #2
    Guest

    Default

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

  3. #3
    Guest

    Default

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

  4. #4
    Guest

    Default

    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...)

  5. #5

    Join Date
    Feb 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •