+ Reply to Thread
Results 1 to 8 of 8

Delete certain rows that are empty and resize those that are not

  1. #1
    Registered User
    Join Date
    09-26-2017
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    4

    Delete certain rows that are empty and resize those that are not

    Hi so I have a spreadsheet that comes to me weekly that I need to do a few things to. Basicly in sheet one I fill in a code and it pulls in data from sheet 2. This results in two sets of data. I want to be able to make a marco that says IF C11:C21 = Blank ( i.e the formula doesn't result in anything) then delete those rows. and then I want to do the same things for C31:33 if = no result from IF forumula blank out those values. I also want to be able to do something that resizes the row height of the rows with data in them. I am doing this now manually via this code :

    Please Login or Register  to view this content.
    Could anybody help me with this. This is what the sheet looks like filled in sometimes it will spit out #N/A in the first section in row d if there is nothing resulting from the formula
    Please Login or Register  to view this content.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete certain rows that are empty and resize those that are not

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    09-26-2017
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete certain rows that are empty and resize those that are not

    Hi,

    Thank you attached is the workbook what I want to automate. See the before and after aka deleting unused cells, resizing the rows and eventually I would like to be able to copy the worksheet and insert the other code 00123B.

    Thanks !
    Last edited by hans222; 09-28-2017 at 09:20 AM.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete certain rows that are empty and resize those that are not

    This will get the task at hand:

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    If you haven't used macros before you'll need to go to:
    File- options - trust center -trust center settings - macro settings ,
    the second option down (disable all macros with notification)

    Then - Copy the code to the clipboard

    Open your Workbook

    Press ALT + F11 to open the Visual Basic Editor.

    Select "Module" from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    *Be sure to save the book with the code as Macro-Enabled

  5. #5
    Registered User
    Join Date
    09-26-2017
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete certain rows that are empty and resize those that are not

    ...deleted
    Last edited by hans222; 09-28-2017 at 09:20 AM.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete certain rows that are empty and resize those that are not

    If you've got the code in a standard module in the book you can just run it each time - but here's code to run the whole book - if they're all the same!

    Please Login or Register  to view this content.
    and - thanks for the rep!

    If the book is like the sample:

    Please Login or Register  to view this content.
    Last edited by xladept; 09-27-2017 at 05:55 PM.

  7. #7
    Registered User
    Join Date
    09-26-2017
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    4

    Re: Delete certain rows that are empty and resize those that are not

    That was awesome... last question I am using this code to pull in the relevent ID number
    Please Login or Register  to view this content.
    is there anyway to get the macro to go into the data sheet and column B and find the code the ends with AA or BB Ie 00123A, 00123B or 00222A and it would pull the code that ends with A or B depending on the sheet referenced. I am doing this because on different weeks the first 5 digits of the code will change.


    Thanks so much!

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete certain rows that are empty and resize those that are not

    Pull it where? another sheet?

+ 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. Protected worksheet with macro but can't delete text or resize rows
    By Triscia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2016, 12:58 PM
  2. Delete Empty Rows and empty columns from the word table
    By mvneema in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2014, 10:51 AM
  3. Delete empty rows
    By indhaka in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-03-2012, 09:27 AM
  4. delete duplicate rows, delete empty rows
    By loade in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-03-2012, 05:42 AM
  5. Autofilter/delete empty rows is deleting non-empty rows!
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2010, 12:31 PM
  6. Delete Empty Rows
    By mlr0911 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2007, 04:00 PM
  7. Delete Empty Rows
    By pegbol in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2005, 12:14 AM

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