+ Reply to Thread
Results 1 to 6 of 6

Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Hi,

    I Have a code that loops and looks through a sheet and if Col A,B,C,D in row are blank it will delete the entire Row.

    The Problem I have is that for some reason when I run the code, it goes down to the last possible cell to delete all the empty rows I guess.

    I do not have the actual code right now as it is on my other lap top.

    But I have attached data and if anyone can provide a better solution it would be much appreciated

    Other things I have tried that takes sooo long:

    Filter for blanks and deleting visible cells...
    Attached Files Attached Files
    Last edited by swade730; 11-01-2016 at 07:52 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,989

    Re: Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Wherever possible avoid looping code, particularly if large ranges are involved and you are not processing the range in a VBA array. They can get really slow.
    The quickest way I know to do this sort of stuff is an Autofilter to filter the blank rows then delete them.

    The file you attached only contains data in column A but assuming you do have columns A:D with data and column E is blank and can be used as a helper column
    then the following macro will delete the blank rows

    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Thank You For your help with this...
    I will mark this thread as solved.

    2 questions,

    1.
    Please Login or Register  to view this content.
    Can I Re-write this as

    Please Login or Register  to view this content.
    and Do you have a useful link for using arrays like you mentioned above?

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,989

    Re: Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Hi,

    You can use code like
    Please Login or Register  to view this content.
    but in general it is not good practice. A sheet tab name can easily be changed and once changed the macro will fall over unless it too is changed. Always use the VBA sheet CODE name which stays the same whatever the tab name happens to be.

    Chip Pearson talks about Ranges and VBA arrays here

    I prefer to avoid populating VBA arrays or writing back to an Excel range with loops wherever possible and therefore tend to use the following sort of code

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    You can use code like
    Please Login or Register  to view this content.
    but in general it is not good practice. A sheet tab name can easily be changed and once changed the macro will fall over unless it too is changed. Always use the VBA sheet CODE name which stays the same whatever the tab name happens to be.

    Chip Pearson talks about Ranges and VBA arrays here

    I prefer to avoid populating VBA arrays or writing back to an Excel range with loops wherever possible and therefore tend to use the following sort of code

    Please Login or Register  to view this content.
    Can you refer to the sheets by index using the method you describe. Sorta Like,
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,989

    Re: Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank

    Quote Originally Posted by swade730 View Post
    Can you refer to the sheets by index using the method you describe. Sorta Like,
    Please Login or Register  to view this content.
    Yes, but the same problem will occur if someone moves the sheet tab to a different position in the tab name bar.

    For any macro in the ActiveWorkbook that operates solely on the Activeworkbook there is no point in using anything other than the sheet code name. Otherwise you leave yourself open to users screwing things up by renaming or moving sheet tabs.
    You can always change the VBA code name if you don't want to stick with the default names Sheet1, Sheet2,...etc.

    From memory I think the only exception when you can't use the VBA sheet code name is if you're addressing a sheet in another workbook. Then you would need to use the sheet tab name.
    Last edited by Richard Buttrey; 11-04-2016 at 06:34 AM.

+ 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] Delete Entire row if cells are blank
    By jamesdean1379 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2016, 01:45 PM
  2. VBA/Macro for delete the entire row if formula result is zero/blank
    By vijay20cbe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2014, 03:19 PM
  3. [SOLVED] Delete entire row if Column C is blank
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2014, 12:03 PM
  4. if cell is blank delete entire row
    By thuddleston11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2013, 03:38 PM
  5. [SOLVED] Delete entire row if column F is blank
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 11:03 AM
  6. Replies: 1
    Last Post: 05-10-2012, 04:58 PM
  7. delete entire blank rows
    By isabella in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2010, 09:07 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