Delete rows that start with CCNUXXXX with macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I want to set a macro which deletes all the rows that in columm B start with
CCNU###### (the other caracters will be 7 numbers).

Could anyone help me with it?

Thanks,
Orquidea
 
Orquidea,

You can do it a non-macro way, using Autofilter, Custom, CCNU*. Then select the filtered
rows and delete them, then remove the Autofilter.

This macro will do it. It stops at the first empty cell it sees in the column.

Sub DeleteRows()
Const Coll = 2 ' column
Const SearchData = "CCNU"
Const StartRow = 2 ' starting row
Dim Roww As Long
Roww = StartRow
Do
If Left(Cells(Roww, Coll), Len(SearchData)) = SearchData Then
Cells(Roww, Coll).EntireRow.Delete
Else
Roww = Roww + 1
End If
Loop While Cells(Roww, Coll) <> ""
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Just to supplement your posting, here is a subroutine that stops at the last
filled cell in the column (whether internal empty cells exist or not)...

Sub DeleteRows()
Const C = 2 'Column
Const StartRow = 2 'Starting row
Const SearchData = "CCNU"
Dim R As Long
For R = Me.Cells(Rows.Count, C).End(xlUp).Row To StartRow Step -1
If InStr(Me.Cells(R, C), SearchData) = 1 Then Cells(R, C).Delete
Next
End Sub

Rick
 
I mistyped Delete instead of ).EntireRow.Delete (see below)....
Just to supplement your posting, here is a subroutine that stops at the
last filled cell in the column (whether internal empty cells exist or
not)...

Sub DeleteRows()
Const C = 2 'Column
Const StartRow = 2 'Starting row
Const SearchData = "CCNU"
Dim R As Long
For R = Me.Cells(Rows.Count, C).End(xlUp).Row To StartRow Step -1
If InStr(Me.Cells(R, C), SearchData) = 1 Then Cells(R, C).Delete
Click to expand...

The above line should be this instead...

If InStr(Me.Cells(R, C), SearchData) = 1 Then Cells(R, C).EntireRow.Delete

Rick
 
Thanks Earl, Autofilter and macro worked

Earl Kiosterud said:
Orquidea,

You can do it a non-macro way, using Autofilter, Custom, CCNU*. Then select the filtered
rows and delete them, then remove the Autofilter.

This macro will do it. It stops at the first empty cell it sees in the column.

Sub DeleteRows()
Const Coll = 2 ' column
Const SearchData = "CCNU"
Const StartRow = 2 ' starting row
Dim Roww As Long
Roww = StartRow
Do
If Left(Cells(Roww, Coll), Len(SearchData)) = SearchData Then
Cells(Roww, Coll).EntireRow.Delete
Else
Roww = Roww + 1
End If
Loop While Cells(Roww, Coll) <> ""
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
Click to expand...
 
Back
Top