VBA to Clear Row Contents Based on Multiple Criteria

  • I am needing a macro to clear row content in Column A:J based on multiple criteria. I have attached excel file with the data and desired result.


    Criteria
    1. EFF DATE COLUMN - Clear Contents of A:J row that are less than today @ 17:45. Note eff.Date is imported data does not contain correct eff time. The time is found in the Eff.Time Column. I need to clear content of old prices that are in the past.
    2. GROSS PRICE COLUMN-Clear Contents of A:J row that are less than 5.
    3. OUTAGE COLUMN-Clear Contents of A:J row that are equal to Y


    Problems
    Can Not CLEAR Data IN Columns K:M because they contain formulas
    Data only needs to be cleared in Column A:J


    Any help would be appreciated.


    Thank you!
    [TABLE="width: 234"]

    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td]

    [ATTACH=CONFIG]67280[/ATTACH]

    [/td]


    [/tr]


    [/TABLE]

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    What do you have as far as code goes? If you're not using a huge data set (tens of thousand of rows) you should be able to accomplish this using For Each loops. It will step through each cell and check criteria using If statements, e.g. If Range("A1").Value <5 Then Range("A1").ClearContents.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    Quote from Halvar;758779

    What do you have as far as code goes? If you're not using a huge data set (tens of thousand of rows) you should be able to accomplish this using For Each loops. It will step through each cell and check criteria using If statements, e.g. If Range("A1").Value <5 Then Range("A1").ClearContents.



    I usually have about 4000 lines that I am needing to go through daily, and the data is reimported each day. I need to be able to clear those values so my formulas can then calculate on the cells that are not blank. The data set changes each day and I reimport them daily in column A-J.


    I have code to clear yesterdays dates below, but it does not take in consideration the date in column D AND the time stamp in column E. Not sure how to concaninate the two columns then clear cells that are greater than today @ 17:45. New Prices go in effect at today 18:00 so those prices will calculated if visible. This code also doesn't clear the content in that line A:J. Nor does it check for other things that also need to be tested for like F:G containing values greater than 5.


    Here is the code I have


  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    BTW: I have used advanced formulas to calculate everything, but it has slowed my spreadsheet down so much it is no longer workable. I'm trying to get away from that by using VBA to clear data I don't need before my calculations are run.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    By 'advanced formulas' I assume you mean array formulas, clearing content will not necessarily speed up such calculations, it may be better to make those calculations within the code that clears the data.


    If you want VBA to replace your formulas then you need to show what those formulas are.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    Quote

    2. GROSS PRICE COLUMN-Clear Contents of A:J row that are less than 5.


    However in your sample you have not cleared rows where Gross Price is less than 5. Rows with a Gross Price equal to 5 have been cleared but those 2 rows also have a Y for Outage so are cleared for that reason anyway.


    Please clarify about Gross Price.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    I'm sorry. I need to clear cells greater than 5 and keep cells less than five

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    From your first sample workbook I thought there were only 3 columns of formulas, not all those columns in your actual workbook.


    Doing the calculations in VBA is not really a viable option with so many formulas.


    This code will clear the data from columns A to J for rows that meet one or more of the criteria. The clearing will be done in one go, not row by row so it may speed up calculation times, but looking at your formulas I am not completely certain it will make any difference.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    Worked great! I will now be able to minimize my formulas due to this code.


    I really appreciate it.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    QUESTION: IF THE DATE AND TIME STAMP ARE IN ONE CELL IN COLUMN D, HOW WOULD I MODIFY THIS TO CLEAR CELLS THAT ARE LESS THAN TODAY @ 17:45. I TRIED BUT IT DIDNT WORK.


    10/23/2015 18:00
    10/17/2015 00:00


    With ActiveSheet.Cells(2, 1).CurrentRegion.Resize(, 10)
    x = .Value
    For i = 3 To UBound(x, 1)
    If (x(i, 4) = Date And x(i, 4) < 0.739583333) _
    Or x(i, 4) < Date Or x(i, 5) > 3.5 Then
    For ii = 1 To 10
    x(i, ii) = Empty

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    It depends on how date and time are getting into a single cell and how that cell is formatted.


    Are you simply combining the values from the 2 cells in the sample file into a single cell or is data being imported as date and time in a single cell?


    Can you attach a sample file with date and time in a single cell.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    I have a DIFFERENT DATA SET THAT BRINGS IN THE DATA IN A LITTLE DIFFERENT FORMAT. Column D has the date and time formatted together. It is very similar to the other data imported.


    Is there are way to clear the cells based on the dated Value in U1. It will then clear based on anytime before that time in that cell. Its hard to know depending on what time we import the data what date and time we need to clear. I think if we could clear cells based on a cell value...it would be easier to clear the correct data. He is my file. Let me know if you have questions.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    Do the criteria for Gross Price (>5) and Outage ("Y") still apply, or is a date and time prior to an entered cut-off tme the only criterion to be used?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    If I entered 10/23/2015 17:45 in a cell <U1>, I would like it to clear A:J in the rows that occur before that time and also clear A:J if column E is >5


    There code u wrote should be real close.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    Try the attached.


    I have changed some of the data for testing purposes.


    Enter required cut-off date and time in cells C1 and E1 respectively then click the "Clear Pre Cut-off Data" button.


    Code in the RACK PRICE worksheet object module is

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to Clear Row Contents Based on Multiple Criteria


    I really like the idea of using a two cells to clear the data. That way I can control the data I want to clear with our changing vba code
    If I put the date stamp in D1 and the time stamp in E1 using the first data set I have tried to modify your second bit of code but can't seem to get it to work.


    so on the customprice2.xlsm file...here is what I'd like to be able to do. Something is not quite right on the code I modified below. I wish I were better at VBA.


    Clear Row A:J that are less than D1 and E1 combined. (D1 and E1 are where I will manually plug in a date and time).
    Clear Row A:J if F is less than 3.5
    Clear Row A:J if G is less than 3.5
    Clear Row A:J if I is "Y"
    Sort


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!