1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete first five cells in a row based on two conditions

Discussion in 'VBA Macros' started by freshtomm, Mar 19, 2017.

  1. freshtomm

    freshtomm Member

    Messages:
    31
    Hello,

    i need to delete first five cells (A:E) in a row in case that cell in column A contains value higher than 2016 and cell in column B contains value higher than 15. Both conditions must be true to delete part of that row. Is that possible?

    Thanks.
  2. vletm

    vletm Well-Known Member

    Messages:
    2,263
    Q1: Do You really want to 'delete' or just 'clear' those cells; "A1:E5"?
    Q2: How many times would needed 'delete' to do; once or as many times as both conditions are true?
  3. freshtomm

    freshtomm Member

    Messages:
    31
    Every week i get database, and i need to delete every record that is not 2016 in clolumn A and from 1 to 15 in column B. But only columns Axxx:Exxx bacause in further columns i have formulas.
    Q1: I want to delete cells and data from below move up.
    Q2: I need to do that as many times as both conditions are true.

    Thanks.
    Derek McGill likes this.
  4. YasserKhalil

    YasserKhalil Active Member

    Messages:
    573
    Try this
    Code (vb):
    Sub Test()
        Dim i As Long
       
        For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            If Cells(i, 1).Value > 2016 And Cells(i, 2) > 5 Then
                Cells(i, 1).Resize(, 5).Delete Shift:=xlUp
            End If
        Next i
    End Sub
    If you need to delete the cells to the left .. Change xlUp part to xlToLeft
    Regards
  5. vletm

    vletm Well-Known Member

    Messages:
    2,263
    @freshtomm
    1st You wrote that:
    i need to delete first five cells (A:E) in a row in case that cell in column A contains value higher than 2016 (no 2015, yes 2017) and cell in column B contains value higher than 15 (no 14, yes 16).
    Both conditions must be true to delete part of that row.

    Now You wrote that:
    Every week i get database, and i need to delete every record that is not 2016 in clolumn A (yes 2015, yes 2017) and from 1 to 15 in column B (yes 14, no 15).

    >>> Did rules change or what happened?
    Are those A- & B-column values numbers?
  6. Derek McGill

    Derek McGill Member

    Messages:
    115
    This might be what you want :-
    ( Use on a copy of your file )
    Code (vb):

    Sub MoveUp_5_Cells()
    Dim Last_Row As Long
    Dim i As Long
    Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row

    Do Until Last_Row = 1

    If Cells(Last_Row, 1).Value > 2016 And Cells(Last_Row, 2) > 15 Then
       Cells(Last_Row, 1).Resize(, 5).Delete Shift:=xlUp
       Else
       Last_Row = Last_Row - 1
    End If
    Loop
    End Sub
     
    freshtomm likes this.
  7. freshtomm

    freshtomm Member

    Messages:
    31
    My mistake, B contains value higher than 15 is right. (no 14, yes 16)
    Both are numbers.
  8. freshtomm

    freshtomm Member

    Messages:
    31
    Both codes works well, but way to slow on that large database.

    Thanks anyway.
  9. Marc L

    Marc L Excel Ninja

    Messages:
    2,916
    Hi !

    As Excel is not a database software,
    in case of serious database, use Access instead …

    If at least you join a sample workbook,
    a faster way using formula in VBA may be shared !

    A trick : column F empty may help …
    Derek McGill likes this.
  10. Derek McGill

    Derek McGill Member

    Messages:
    115
    Try to give as much information at the start to help the people who want to help you.
  11. Marc L

    Marc L Excel Ninja

    Messages:
    2,916
    Yasser, Derek,
    here it is just the first VBA rule to keep in mind : TEBV (1)
    Better than using a loop and working cell by cell or row by row
    (the more rows, the slower) is to use Excel basics !

    • In a helper column, F as here it's just column A to E,
    just apply an easy formula (at very beginner level !)
    testing the cells, example in F2 : =AND(A2>2016,B2>15)
    Result displays FALSE for a row to keep and TRUE if to be deleted …

    • Sort range from column A to F on column F in ascending way :
    the rows to delete (TRUE) are now at the end of range …

    • Use Find or MATCH Excel function on column F to get the first TRUE
    so the first row to delete.

    • From this first row to delete until the last row, use Range.Clear method
    as it is faster than Delete and 'cause all rows to delete are yet at the end,
    no needing to move up any row under ! Clear too column F …

    (1) Think Excel Before VBA ! That's it ! Whatever you live in Egypt or in Bulgaria …

    Edit : after a private message from Bulgaria (yes Chandoo is World Wide !)
    I must precise : this post is a reminder to use Excel basics
    within a VBA code as it can be faster than a classic VBA loop …

Share This Page