VBA delete empty rows that start with a determined text

[mickeystanford_alumni]

Hi all,

I hope you are good.
I am trying to do the following: After having a database with thousands of rows, some of them appear as empty. Also, 99% of them follow a determined format, but there are a few which look totally different. Therefore, I would like to do the following:
1. All empty rows, delete (shift up).
2. The rows that start with ALL_COUNTRY in column A, delete entire row.

Thank you so much.

 

[Jimmypop]

Good day....

Try the following:

1.

Private Sub CommandButton1_Click()
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
End Sub

2.

Private Sub CommandButton2_Click()
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*" & InputBox("Search for?") & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

 

[Peter_SSs]

some of them appear as empty.

That is a bit worrying as cells that 'appear' empty can contain space characters or other non-visible characters.

However, assuming that the cells are empty or contain zero length data, if your data is large, this should be a very fast way to delete the rows.

Sub Del_Blank_And_AllCountry()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Len(a(i, 1)) = 0 Or a(i, 1) Like "ALL_COUNTRY*" Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

If neither of the above suggestions do what you want, please give us some small dummy sample data with XL2BB so the we can test with that.

 

[mickeystanford_alumni]

Both of them working sirs, appreciate it a lot.
Have a great day ahead.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.