Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Delete rows based on word in column across sheets

0

Hi

I try using code contains array to deal with big data for each sheet until running code without any slowness, but I face error subscript ou of range in this line

 b(k, j) = a(i, j)

the code should delete rows contains BALANCE word in column A  for every sheet contains BALANCE word in column A .

Sub deleterows_v2()
  Dim sh As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  For Each sh In Sheets

  a = sh.Range("A2:H" & sh.Range("H" & 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 a(i, 1) <> "BALANCE" 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
  Next
End Sub

any chance to correct the code,please?

Answer
Discuss

Answers

0
Selected Answer

Hi again Hasson

In the attached revised file, I've modified your code as shown in bold below.

I added a new variable DelStr for flexibility (it can be changed for other scenarios), reset your row counter k and added a whole section near the end to clear the existing values (except header row 1),  add ONLY the "filtered" data and add borders to that range only.

Beware- when you run this, it will overwrite your original data (with the "BALANCE" rows). You can choose to save or not save that result (and re-run the code) of course.

Sub deleterows_v3()
  Dim sh As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim DelStr As String

  ' change this as required, e.g. to TOTAL
  DelStr = "BALANCE"

  For Each sh In Sheets
    a = sh.Range("A2:H" & sh.Range("H" & Rows.Count).End(3).Row).Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    ' reset counter
    k = 0

    For i = 1 To UBound(a, 1)
        ' don't copy lines with DelStr value in column A to array b
        If a(i, 1) <> DelStr Then
          k = k + 1
          For j = 1 To UBound(a, 2)

            b(k, j) = a(i, j)
          Next j
        End If
    Next i

    'clear the target range (after headers)
    With sh.Range("A1")
        .CurrentRegion.Offset(1, 0).Clear
        ' add results but ignoring unfilled rows
        With .Offset(1, 0).Resize(k, UBound(b, 2))
            .Value = b
            ' add borders
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End With
    End With
  Next sh
  ' tell user what happened
  MsgBox "All rows containing " & DelStr & " removed (case-sensitive)"

End Sub

Hope this fixes your problem. If so, please mark this Answer as Selected.

Discuss

Discussion

Thanks John for this work.
Hasson (rep: 32) Jul 2, '24 at 12:14 pm
Glad that helped. Thanks for selecting my Answer, Hasson.
John_Ru (rep: 6297) Jul 2, '24 at 1:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login