• 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...

  • 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

freshtomm

Member
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.
 
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?
 
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.
 
Try this
Code:
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
 
@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?
 
This might be what you want :-
( Use on a copy of your file )
Code:
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
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?
My mistake, B contains value higher than 15 is right. (no 14, yes 16)
Both are numbers.
 
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 …
 
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 …
 
Back
Top