+ Reply to Thread
Results 1 to 30 of 30

Help removing blank cells in range

  1. #1
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Help removing blank cells in range

    Hi

    I need some help trying to delete blank cells in a range and shifting them up.

    In the example below, I want to check each cell in the range from D4 to last row and if ALL of the cells are blank in each range, e.g. A4 to J4, then delete the cells from A4 to J4 and shift up. Then repeat for all the cells in the range.

    I have written the following but nothing is happening? Can anyone please assist?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help removing blank cells in range

    Hi maym,

    Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    Try this code instead.

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    Hi Robert, I tried yours but nothing happens. I also noticed that it says delete entire row but I only want range as I also need to do the same across the sheet with other ranges.

    quekbc - When I ran yours, it moved rows up but did not delete the blanks

  5. #5
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    sample attached.

    In the example, it should delete the range A to J for rows 8,10,11.

    I will then need to the same across the sheet (with different ranges).
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    It'll be good to have more information on it. Even better if you could upload a sample file.

    Where are the blank cells you are referring to. The macro deletes columns 1 : 10 (i.e. A:J) when there are blank cells in column A only. Did you want to delete any lines that have at least 1 blank cell in D:J?

    Edit: Just saw the attached file.

  7. #7
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    I understand now. Try this code.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Help removing blank cells in range

    Hi Robert, I tried yours but nothing happens.
    Correct - because in your first post you said "of the cells are blank in each range, e.g. A4 to J4, then delete the cells from A4 to J4 and shift up" yet in the posted workbook there are no rows that are blank across columns A to J (inclusive) so yes nothing happens It actually seems the columns to check are D to L (inclusive).

    Seems like quekbc is on to this so I'll leave you in their capable hands.

    Robert

  9. #9
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    Hi quekbc, that works perfect now, thanks

    Is there a fast way to do this across the sheet, or would I need to use the code and copy it? Essentially I need to do it 4 times for:

    Range D4:J (lastrow) (delete A to J) YOUR CODE
    Range P4:V (lastrow) (delete M to V)
    Range AB4:AJ (lastrow) (delete Y to AJ)
    Range AN4:AT (lastrow) (delete AK to AT)

  10. #10
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    Quote Originally Posted by Trebor76 View Post
    Correct - because in your first post you said "of the cells are blank in each range, e.g. A4 to J4, then delete the cells from A4 to J4 and shift up" yet in the posted workbook there are no rows that are blank across columns A to J (inclusive) so yes nothing happens It actually seems the columns to check are D to L (inclusive).

    Seems like quekbc is on to this so I'll leave you in their capable hands.

    Robert
    thanks Robert. Sorry I wasn't totally clear in my post.

  11. #11
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    Yes, there is. They're all 12 columns apart it seems. so...

    Here goes (save your file first, just in case).

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help removing blank cells in range

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    Hi quekbc - it's starts ok but then I get an "Object required" error on this line:

    Please Login or Register  to view this content.
    jindon - your code works but only for the first section up until col J. I need this for the entire spreadsheet as explained above.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help removing blank cells in range

    If you go uptp
    Range AN4:AT (lastrow) (delete AK to AT)
    Nothing will be left on your smaple
    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help removing blank cells in range

    Correction
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    thanks jindon. It works for the ranges through to col J, but not the rest?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help removing blank cells in range

    See my previous post.

  18. #18
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    thanks jindon, but it still doesn't delete the rest of the ranges (past col J)?

  19. #19
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    Looks like jindon's all over it. I'm not sure why an issue is coming up with rngCheck on your version. Here's a corrected version if it does anything.

    Please Login or Register  to view this content.
    This code seems to run fine in the sample file you sent.

  20. #20
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    thanks quekbc. It works fine now, I just changed this:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.
    as I will still getting the "Object Required" error.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help removing blank cells in range

    I need to go out so not time today
    Please Login or Register  to view this content.

  22. #22
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    Glad it worked! Not sure why that didn't occur to me.

  23. #23
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    unfortunately I tried to run it again but keep getting the same error. I 'm not sure why it works for you but not me? Nothing has changed in terms of code.

    jindon - this still leaves blank cells in the sheet.

  24. #24
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    That's odd. Are you able to upload a sample file where the error will occur? Can't really debug it if I can't replicate the error.

    Also... try this - at the very first rngCheck, do.

    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    sure, here it is attached. I cannot seem to work it out!
    Attached Files Attached Files

  26. #26
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    Ahah! I see the problem now - an unanticipated one.

    Basically, as part of the deletion process, I accidentally deleted the cells that the rngCheck object was defined as - which made it disappear. So, let's move the cells to start looking at row 3 (where the headers are at) - and as the headers are always populated, it should never be deleted.

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    ah ok I see. That is working perfectly now, thank you quekbc!

  28. #28
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Help removing blank cells in range

    No worries. Glad to help.

  29. #29
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    Hi

    sorry to bring up an old thread. I am using this code above which works perfectly (thanks quekbc).

    However I have changed my ranges, the first range now starts at D3:P (previously D3:J) in this line:

    Please Login or Register  to view this content.
    How can I adjust to now go across my new ranges?

  30. #30
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Help removing blank cells in range

    I have managed to amend to the following:

    Please Login or Register  to view this content.
    Can anybody verify if this is actually correct or if I am deleting rows that I shouldn't be? I didn't quite understand the last parts of the code that set the rows to delete?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing blank cells
    By Notters in forum Excel General
    Replies: 4
    Last Post: 02-07-2014, 09:12 AM
  2. Removing blank cells from csv
    By steve777888 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2013, 10:43 AM
  3. Removing Blank Cells
    By pickslides in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2011, 12:48 AM
  4. removing blank cells
    By kmknowles in forum Excel General
    Replies: 17
    Last Post: 03-04-2010, 05:14 PM
  5. Removing blank cells
    By eclipsenetworkz in forum Excel General
    Replies: 2
    Last Post: 05-16-2008, 07:49 PM
  6. [SOLVED] Removing blank cells...
    By Jambruins in forum Excel General
    Replies: 0
    Last Post: 03-31-2006, 03:10 PM
  7. Removing blank cells
    By Dallas101 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2006, 02:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1