Delete rows that start with CCNUXXXX with macro

[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

 

[Earl Kiosterud]

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...

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

The above line should be this instead...

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

Rick

 

[Guest]

Thanks Earl, Autofilter and macro worked

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...

 

[Guest]

Thanks Rick for your help. It worked very well.

Orquidea