exclude multiple words when delete entire row for two columns

Hasson

Active Member
Joined
Apr 8, 2021
Messages
406
Office Version
  1. 2016
Platform
  1. Windows
Hello
I have about 15000 rows .
I want deleting entire row based on match on column B . theses words (SALES,BUYING,STOCK) shouldn't delete at all , other meaning should exclude from deletion , otherwise delete the others items in column B, I have another case in column A there are dates and some rows contains TOTAL word so should delete entire row for column A contains TOTAL word .
the data will be in range A:E and in Sheet1 and the result should implement for the same sheet .
thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try this:
VBA Code:
Sub deleterows()
  Dim sh As Worksheet
  Dim rng As Range
  Dim i As Long, lr As Long
 
  Set sh = Sheets("Sheet1")
  lr = sh.Range("B" & Rows.Count).End(3).Row
  Set rng = sh.Range("B" & lr + 1)
 
  For i = 1 To lr
    Select Case UCase(sh.Range("B" & i).Value)
      Case "SALES", "BUYING", "STOCK"
      Case Else: Set rng = Union(rng, sh.Range("B" & i))
    End Select
    If UCase(sh.Range("A" & i).Value) = "TOTAL" Then: Set rng = Union(rng, sh.Range("B" & i))
  Next
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  rng.EntireRow.Delete
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

If the process is slow and you don't have formulas in the sheet, then try the following macro:
VBA Code:
Sub deleterows_v2()
  Dim sh As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  Set sh = Sheets("Sheet1")
  a = sh.Range("A1:E" & sh.Range("B" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1)
    If UCase(a(i, 2)) = "SALES" Or UCase(a(i, 2)) = "BUYING" Or UCase(a(i, 2)) = "STOCK" Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  sh.Range("A1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 1
all of your codes are great !
but where is this in second code? !
VBA Code:
    If UCase(sh.Range("A" & i).Value) = "TOTAL" Then: Set rng = Union(rng, sh.Range("B" & i))
should also delete TOTAL word in column A as first code .
 
Last edited:
Upvote 0
The codes have different logic.
The first deletes the rows that meet the requirements.

The second code does not delete, the second code only keeps the data that the words have.
Assume that in the same row you don't have "TOTAL" in A and you don't have "SALES", "BUYING", "STOCK" in B in the same row.
;)



But if it were the case where in the same row you have TOTAL and some of the words, then try this:
VBA Code:
Sub deleterows_v2()
  Dim sh As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  Set sh = Sheets("Sheet1")
  a = sh.Range("A1:E" & sh.Range("B" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1)
    If (UCase(a(i, 2)) = "SALES" Or UCase(a(i, 2)) = "BUYING" Or _
       UCase(a(i, 2)) = "STOCK") And a(i, 1) <> "TOTAL" Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  sh.Range("A1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

🫡
 
Upvote 0
Solution

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top