+ Reply to Thread
Results 1 to 9 of 9

Changing dates and removing rows with VBA

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    105

    Changing dates and removing rows with VBA

    Hello, I have a pre-existing code that has been written before my time. I would like to modify it since I am taking over the excel spreadsheet. The way that this all works is very complicated, but what I need to be done should be straight forward in one section of the code.

    There are a total of 3 excel spreadsheets that are open. The main spreadsheet has a button that copies all the cells from the other 2 spreadsheets and dumps them into 2 different tabs for proper formatting and analysis.

    What I need to add:
    Before the cells are selected in one of the spreadsheets I would like the data to be cleaned up a bit.

    1. In Column J "Date Made" I would like to have all dates to be changed to the earliest date found. So let's say that column J has 10/22/2019;10/23/2019;10/21/2019; I would like everything to be changed to 10/21/2019 because it is the lowest date.

    2. In Column K "WO#" I would like the entire row to be deleted if a number starts with "9". So if row 10 has a number that starts with 9 in Column K, delete row 10.

    Section of the code that I think that this needs to be added to from Module 1 (Full code attached):

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,436

    Re: Changing dates and removing rows with VBA

    I have not integrated the code in you Macro as you did not supply sample data.

    This should get you started.


    This Macro will change your Dates.

    Please Login or Register  to view this content.

    This Macro will delete allrow starting a 9 in column K.
    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-25-2019 at 01:00 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    105

    Re: Changing dates and removing rows with VBA

    One small problem, I forgot to mention that these are special cases. Most of the time all the dates are the same and there are no numbers that start with 9. It's the special cases that screw everything up.
    So when I ran the code that you have written, I got an error saying that there are no numbers that start with 9 in Column K. I also had to correct it from Column L to K.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,436

    Re: Changing dates and removing rows with VBA

    So is this solved?

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    105

    Re: Changing dates and removing rows with VBA

    No, I cannot get it to work at all. I created a separate sub for the code and I keep getting the same error: No cells were found (even though there are rows that need to be deleted). In this case, it would be row 28 and 29.

    Picture attached.
    Attached Images Attached Images
    Last edited by YuriyBaron; 10-25-2019 at 03:29 PM.

  6. #6
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,436

    Re: Changing dates and removing rows with VBA

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). 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.

  7. #7
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    105

    Re: Changing dates and removing rows with VBA

    I have attached a sample spreadsheet. The data that needs to be changed is on the "WOFile" Tab. Would you please create a new module and insert the code there. I will then incorporate it into the proper button.

    Thank you.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,436

    Re: Changing dates and removing rows with VBA

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    105

    Re: Changing dates and removing rows with VBA

    Ok, I messed around with the code that you have written and was able to get it to work properly. Big thanks for writting this.
    Final Code:

    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] Removing empty rows from a range without changing worksheet layout
    By aglawrence in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-15-2018, 07:30 AM
  2. Formulas changing when adding/removing rows from tables
    By jhuvba in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-09-2018, 10:24 AM
  3. [SOLVED] removing rows & dates from a vlookup table
    By jrtaylor in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-31-2018, 12:26 PM
  4. [SOLVED] Worksheet Automation -- Changing start dates and end dates of growth curves & acct numbers
    By adam_d_john in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-06-2017, 06:20 PM
  5. changing or removing unreliable data
    By Zydrunas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2015, 04:51 AM
  6. removing hyperlinks without changing the cell format
    By Bronto in forum Excel General
    Replies: 0
    Last Post: 08-21-2014, 12:21 PM
  7. Calculating Hours Between 2 Dates & Removing Weekend Dates
    By dbennett@bennettgraphics.com in forum Excel General
    Replies: 4
    Last Post: 10-31-2005, 05:05 AM

Tags for this Thread

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