How to delete rows in excel with VBA

Please post a workbook with these rows.

What do you mean by "automatically"?

Expert: aikimark, per your request.

Attached is a sample file.  I need to add VBA to my macro to delete all the instances with rows in wihch Cell A contains

Constr.#  -  -  -  -  -

EXAMPLECONSTR.xlsx

In this routine, I build ranges of rows to delete and then perform a single .Delete for the combined (super) range.

Note:
Another approach would be to iterate through the rows in bottom-to-top order and do a delete on each row.

Option Explicit

Public Sub DeleteConstRows()
  'Delete rows containing "Constr.#  -  -  -  -  - " in column A
  Dim colDel As New Collection  'row numbers to be deleted
  Dim vRow As Variant
  Dim lngStartDel As Long
  Dim lngEndDel As Long
  Dim rngcell As Range
  For Each rngcell In Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A1").End(xlDown))
    If rngcell.Text = "Constr.#  -  -  -  -  - " Then
      colDel.Add rngcell.Row
    End If
  Next
  Application.ScreenUpdating = False    'for best performance
  'Delete contiguous row ranges
  If colDel.Count = 0 Then
    Exit Sub
  Else
    vRow = colDel(1)
    Set rngcell = ActiveSheet.Cells(vRow, 1).EntireRow
    lngStartDel = vRow
    lngEndDel = lngStartDel
  End If
  For Each vRow In colDel
    If vRow = (lngEndDel + 1) Then
      lngEndDel = lngEndDel + 1
    Else
      Set rngcell = Union(rngcell, Range(ActiveSheet.Cells(lngStartDel, 1), ActiveSheet.Cells(lngEndDel, 1)).EntireRow)
      lngStartDel = vRow
      lngEndDel = lngStartDel
    End If
  Next
  Set rngcell = Union(rngcell, Range(ActiveSheet.Cells(lngStartDel, 1), ActiveSheet.Cells(lngEndDel, 1)).EntireRow)
  rngcell.Delete
  Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Here is the row-by-row deletion example.

For the few rows we are deleting in this workbook, the two routines perform identically.  I would expect the first routine to perform faster with larger workbooks.

Option Explicit

Public Sub DeleteConstRows2()
  'Delete rows containing "Constr.#  -  -  -  -  - " in column A
  Dim lngEnd As Long
  Dim lngRowDel As Long
  Application.ScreenUpdating = False    'for best performance
  lngEnd = ActiveSheet.Range("A1").End(xlDown).Row
  For lngRowDel = lngEnd To 1 Step -1
    If ActiveSheet.Cells(lngRowDel, 1).Text = "Constr.#  -  -  -  -  - " Then
      ActiveSheet.Rows(lngRowDel).Delete
    End If
  Next
  Application.ScreenUpdating = True
End Sub

Select allOpen in new window

They both worked; however, for some reason it left 4 rows at the bottom.  Not sure why.  The text is the same.

If I have to I can work with it.  Thanks

Does the worksheet contain any empty cells in column A?

No, but it  deleted all but 4  of the Constr.#   -  -  -  -  -       Constr.#  rows.

Constr.# 6000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 6000
Constr.# 7000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 7000
Constr.# 8000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 8000
Constr.# 9000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 9000
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#  -  -  -  -  -       Constr.#
Constr.#  -  -  -  -  -       Constr.#

I see seven "Constr.#   -  -  -  -  -" rows, not four.

Are you talking about the four rows at the top?!?

After I ran the syntax, the results were:

It didn't delete all the Constr.#   -  -  -  -  -       Constr.# rows.  It only removed 3 of them, not all 7.

ORIGINAL

Constr.# 6000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 6000
Constr.# 7000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 7000
Constr.# 8000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 8000
Constr.# 9000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 9000
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#  -  -  -  -  -       Constr.#
Constr.#  -  -  -  -  -       Constr.#

I ran the syntax and I ended up with

RESULT

Constr.# 6000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 6000
Constr.# 7000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 7000
Constr.# 8000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 8000
Constr.# 9000 - G1000 - TEST CLL - 23%--- -  <  4 Weeks - Supplier Restriction      Constr.# 9000
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#
Constr.#   -  -  -  -  -       Constr.#

please post that workbook.  I suspect the cell contents are different.

Thats what I was thinking; however, there shouldn't be a difference.  I've attached a sample

It looks like its not deleting row 11, 12, 13, & 14.  I cannot figure out why not.
EXAMPLECONSTR.xlsx

Because these cells are different.  I've pasted the contents of the two different cell values (in delete candidates) below:

"Constr.#   -  -  -  -  - "
"Constr.#  -  -  -  -  - "

Select allOpen in new window

Thanks for your patience.  Works perfectly.