Homer Posted August 13, 2009 Share Posted August 13, 2009 I'm not sure if this is possible, but worth a try... Is there any way to have Excel delete empty cells from a spreadsheet? See attached, sheet 1 has the input, I need to delete the empty cells and shift the remaining ones left as shown on sheet 2. It's every 'other' cell on columns A and C. I know it can be done manually but there's something like 2000 rows in the sheet... Quote Link to comment Share on other sites More sharing options...
MisterSheen Posted August 13, 2009 Share Posted August 13, 2009 Can you not apply a filter to column A, and filter it upon 'non blanks'? Quote Link to comment Share on other sites More sharing options...
Scott Posted August 13, 2009 Share Posted August 13, 2009 I'm not sure if this is possible, but worth a try... Is there any way to have Excel delete empty cells from a spreadsheet? See attached, sheet 1 has the input, I need to delete the empty cells and shift the remaining ones left as shown on sheet 2. It's every 'other' cell on columns A and C. I know it can be done manually but there's something like 2000 rows in the sheet... I've attached the file with a little macro. It will work on any spreadsheet. To copy it to a different spreadsheet go to "view" "Toolbars" and make sure "Visual basic" is selected. Click on the VBA icon (window with blue bar, white contents and red and yellow things in them).Right click on "userform1" and click "export file" Save it where you like. Open up the one you want to add the userform to and go through the above to get to the VBA editing screen. Right click in the window on the left (where you exported from) and click "import" import the file and its done. To run it just click on the userform and then click the play button. Press the "remove blanks" button and it will do the magic for you. You can test it on your original sheet by doing the above as well, click on the VBA icon, click on "userform" and click the play button. HTH Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 Excellent, thanks Scott! It seems to work well and removes the blanks from column A, but is not doing it on column D. Should the code cover all columns? Quote Link to comment Share on other sites More sharing options...
Hermit Posted August 13, 2009 Share Posted August 13, 2009 Right click UserForm1 and then 'View Code'. Insert this line after the one beginning with If: If Cells(row1, 4) = "" Then Cells(row1, 4).Delete Shift:=xlToLeft Sorts it out I was having a go as well, but Scott beat me to it, and his way is a lot neater Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 Cheers mate Knew I could reply on people here to come up with easy answers Quote Link to comment Share on other sites More sharing options...
vvteye Posted August 13, 2009 Share Posted August 13, 2009 Cheers mate Knew I could reply on people here to come up with easy answers That's easy? Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 Okay, got another one for you guys. I was originally going to delete the times, but it'd be nice to keep them if possible.. Is there any way to have the cell values in A2, A4, A5, etc copy it's result to the cell above, see attached Quote Link to comment Share on other sites More sharing options...
Scott Posted August 13, 2009 Share Posted August 13, 2009 Sorry about that, in my haste i didn't read your query properly and only made the code for the first column. Sorted now though I'll have a look at the 2nd one too. Edit: That should do it If thats what your looking for lol. Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 Legend mate! That worked perfectly Really must take some time to learn VB! I'm going to push my luck now and ask for a 3rd bit of help Is there a code for inserting a cell and shifting the row right on every 2nd row. I.e. insert blank cell into B2, B4, etc shifting each row right. You can probably guess where I'm going with this... Quote Link to comment Share on other sites More sharing options...
Scott Posted August 13, 2009 Share Posted August 13, 2009 Legend mate! That worked perfectly Really must take some time to learn VB! Its a fantastic tool, i use it all the time. I'm completely self taught though which means i often do things very very wrong. I never use declerations or anything where i should etc. It works though, so i don't care I only know simple code also, anything more complex than that and i'm fooked. Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 It's still really good stuff though and saves SO much time. It'd be really useful to know more VB for work as i'm working with Excel data at least an hour a day... If you didn't guess already these are the results from the timing computer at Suprapod. PS: I edited above with another request Quote Link to comment Share on other sites More sharing options...
Hermit Posted August 13, 2009 Share Posted August 13, 2009 Is there a code for inserting a cell and shifting the row right on every 2nd row. I.e. insert blank cell into B2, B4, etc shifting each row right. You can probably guess where I'm going with this... I can't guess the 'why' on that one... won't that put the columns out of whack? Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 It will but then can use the delete blank cells code on column E to re-allign them. See attached for what I want to do. I'll then reuse the code to copy the values in B to the cell below. The B column in the lower sheet is the number of the drag run. Edit - hope that makes sense Quote Link to comment Share on other sites More sharing options...
Hermit Posted August 13, 2009 Share Posted August 13, 2009 Ah, I see, I hadn't followed the last change properly Quote Link to comment Share on other sites More sharing options...
Homer Posted August 13, 2009 Author Share Posted August 13, 2009 No matter now, just did that last one manually. Feel a bit cross eyed now tho! Quote Link to comment Share on other sites More sharing options...
Scott Posted August 13, 2009 Share Posted August 13, 2009 Sorry bud, i'm in work so was only on during my teabreak lol. I guessed it was times from the strip. Good work Quote Link to comment Share on other sites More sharing options...
Hermit Posted August 13, 2009 Share Posted August 13, 2009 Likewise, sorry,, but my kitchen ceiling has collapsed :( Quote Link to comment Share on other sites More sharing options...
Scott Posted August 13, 2009 Share Posted August 13, 2009 Your excuse wins Quote Link to comment Share on other sites More sharing options...
Hermit Posted August 13, 2009 Share Posted August 13, 2009 Dinner's ruined lol Water leak fro somewhere, the joy of living in the bottom flat eh Told the landlord on Monday there was a leak dripping through, he hasn't been to look yet. This should hurry him up! (I hope...) It reeaally stinks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.