+ Reply to Thread
Results 1 to 10 of 10

Delete rows

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Delete rows

    Hi,

    I am a VBA novice in search of solution that is probably very simple. I have a Column H which has a bunch of dates. I am trying to create a VBA that will delete all rows with dates in Column H that are great than 45 days from today. If this issue is better suited for a formula of some sort, please advise.


    Bonus:
    Is there a macro that will insert a blank row between data subset and fill it yellow? For example: I have 100 records (row A1:A100 in the eastern market. Immediately below that is the data for the western market, which is an additional 250 record (row A101:A351). I want to leave everything as it is and simply insert a blank row to divide eastern from western. then shade it yellow . If this is too much, please this regard as it is not as important. I just thought it'd make my report cleaner.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,706

    Re: Delete rows

    Try this bit of code:


    Please Login or Register  to view this content.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Re: Delete rows

    I received a debug error for If Date - Range("H" & i) > 45 Then Can't execute code in break mode

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,706

    Re: Delete rows

    Post your sample spreadsheet as I ran this code with a list of dates in column H and it worked without incident.

    What is the error message you get? Are the values in column H dates or text?

  5. #5
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Re: Delete rows

    The message was Can't execute code in break mode. There was an arrow pointing to this line code...... If Date - Range("H" & i) > 45 Then

    Here's a sample of Column H

    expire_dt
    09/15/14
    09/15/14
    09/15/14
    Last edited by lashellr; 10-01-2014 at 10:55 AM.

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,706

    Re: Delete rows

    Looking at your data in the post does not help me to help you. That is why I asked to see your spreadsheet or at least a small sample of it with the applicable fields.

    What happens if you run the code not in break mode? Does it work? You did not respond to my question about the dates. Are they truly formatted as numerical dates or text? If they are text, then this code will probably fail.

  7. #7
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Re: Delete rows

    Please find the requested information attached. I also added a second tab with screen shots of one of the errors. The dates are note formatted as text.
    Attached Files Attached Files
    Last edited by lashellr; 10-03-2014 at 02:03 PM.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete rows

    Try:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-29-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    45

    Re: Delete rows

    Worked like a charm. Thank you both so much!!

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete rows

    For the second part:

    Please Login or Register  to view this content.

+ 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] how to delete delete rows with criteria and keeping formula
    By Bremmah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2013, 07:09 AM
  2. [SOLVED] Delete blank rows between data rows, shift rows up, then repeat
    By excelactuary in forum Excel General
    Replies: 2
    Last Post: 03-11-2013, 11:53 AM
  3. Replies: 5
    Last Post: 11-12-2012, 08:38 PM
  4. Macro to delete certain columns and delete rows based on time in another column
    By beepbeep27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2012, 11:47 AM
  5. delete duplicate rows, delete empty rows
    By loade in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-03-2012, 05:42 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