Delete rows that start with CCNUXXXX with macro
 

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

Delete rows that start with CCNUXXXX with macro
 

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...
-----------------------------------------------------------------------
"orquidea" wrote in message
...
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

Delete rows that start with CCNUXXXX with macro
 

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


"Earl Kiosterud" wrote in message
...
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...
-----------------------------------------------------------------------
"orquidea" wrote in message
...
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

Delete rows that start with CCNUXXXX with macro
 

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



Next
End Sub

Rick


"Earl Kiosterud" wrote in message
...
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...
-----------------------------------------------------------------------
"orquidea" wrote in message
...
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

Delete rows that start with CCNUXXXX with macro
 

Thanks Earl, Autofilter and macro worked

"Earl Kiosterud" wrote:

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...
-----------------------------------------------------------------------
"orquidea" wrote in message
...
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

Delete rows that start with CCNUXXXX with macro
 

Thanks Rick for your help. It worked very well.

Orquidea

"Rick Rothstein (MVP - VB)" wrote:

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



Next
End Sub

Rick


"Earl Kiosterud" wrote in message
...
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...
-----------------------------------------------------------------------
"orquidea" wrote in message
...
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