Hi there,
I am trying to find a method for deleting empty cells in a range and shift cells left upon deletion automatically. I could do this manually, but with 3000 rows of data it could be a long task. I have attched a small spreadsheet to show what I am looking for.
I hope someone can help me! (again)
Last edited by kiboodez; 10-07-2008 at 01:13 PM.
Please Login or Register to view this content.
Hi,
Even though this is in the Excel Functions area, the following VBA procedure is one way and maybe easier than a complex set of function/fomulae
Name the cell at the top of your range ("Top"), i.e the Z cell in the example, and name a cell where you want the output to appear "Output"
Now run the following procedure:
HTHPlease Login or Register to view this content.
I have pasted both of the above selections in as code, as I am not a VBA user I do not know how to get them to run, or how to change the data range they work with.
Please help a useless man in distress!
Hi,
You can name cells by going to the formula bar and with your cursor on the cell (or range) you want to name, just enter the name. In the case two names "Top" for cell B1, and "Output" for B10 in this example.
Now hit the ALt-F11 Keys to take you into the VB Enviroment. In the Project - VBAProject window on the left, double click on the 'ThisWorkbook' item uder your workbook, and in the big code window that opens on the right, paste the code I gave you earlier.
Now Al-F11 back to Excel
In the Tools Macro Macros menu find the macro called 'ShiftValuesLeft' and run it.
If you're going to use it frequently I suggest you add an object/picture/icon or something to the ws, and the right click and attach the macro to the object so that you can run it just by clicking the object.
HTH
HTH
Regards
Thanks very much for the help. I love this forum, I learn more and more everyday!
Hey Richard - just wanted to say thanks...you saved me oodles of time on a huge project : )!!
Re: Delete empty cells, and shift left
Richard,
This vb macro works great... for up to 7 columns and up to 6500 rows...
I am working with data that has 9 columns and 15000 rows. Is there something I'm missing? Is there an edit to this code that i can perform that would delete cells past column 7 and row 6501?
Thanks,
Paul
Hi Paul,
Apologies but I've only just seen this.
The macro uses the .CurrentRegion property of the range and this will cover all contiguous rows & columns. The fact that you state it's not covering all your data suggests that you have a blank column after col 7 and a blank row after 6501.
If you know the last row and column then you could simply set the iRows and iColumns variables to be whatever numbers you want, e.g 15000 & 9
Regards
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sorry for resurrecting an old thread, but I am trying to accomplish the same thing as the OP. I have a spreadsheet with functions in each cell. Some cells have a blank result. I would like to delete the empty cells and shift everything left. Can I use vlookup? I'm not familiar with Visual Basic. Any advice would be appreciated.
WOW! Like mccarthyxa said, this code is awesome! Saved me so much time.
Hi guys,
I posted the original question for this some time ago, and have been using the fantastic code ever since!
I now have a small addendum though, any help would be appreciate.
I am looking to achieve the same thing, but rather than deleting empty cells, deleting those with no colour - any suggestions appreciated.
Hello,
I spoke too soon. The code is great but seems to have a limit. I have 85 columns and 48,000+ rows. When I run the macro, I get a "Run-time error '6': Overflow" message. Any ideas on how to fix this?
Thanks!
Msawyer,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Sorry about that. I will post a new thread.
Thanks so much for this, I also needed it badly
What a wonderful little piece of code!!! So many keystrokes saved!!
Many thanks to Mr. Buttrey!
HI, sorry for resurrecting an old thread but i've tried this macro and I get a message saying Microsoft Visual Basic - 400. I can't figured out what I've done wrong, any help would be much appreciated.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks