Delete rows based on text value in a range
 

I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?

Delete rows based on text value in a range
 

Hi,

Right click your sheet tab, view code and paste this in. Change the text
(Cirrectly TEST) to the text you want but it must be in uppercase.

Sub marine()
Dim myrange As Range, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
If UCase(Trim(c.Value)) = "TEST" Then 'Change to suit must be upercase
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then MyRange1.Delete
End Sub


"Opal" wrote:

I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?

Delete rows based on text value in a range
 

The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Opal" wrote in message ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?

Delete rows based on text value in a range
 

On Sep 15, 1:32*pm, "Ron de Bruin" wrote:
The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. *I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. *I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. *I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")


it works, but I wondered if there was a more effective way to achieve
my results.

Delete rows based on text value in a range
 

You can also use a range with the words
Easier to change then

See this tip for the first macro on this page
http://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Opal" wrote in message ...
On Sep 15, 1:32 pm, "Ron de Bruin" wrote:
The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")


it works, but I wondered if there was a more effective way to achieve
my results.

Delete rows based on text value in a range
 

On Sep 17, 11:34*am, "Ron de Bruin" wrote:
You can also use a range with the words
Easier to change then

See this tip for the first macro on this pagehttp://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") *with *Sheets("Sheet1").Range("A1:A200")

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 15, 1:32 pm, "Ron de Bruin" wrote:





The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

*myStrings = Array("Ron", "Dave", "Tom")

it works, but I wondered if there was a more effective way to achieve
my results.- Hide quoted text -

- Show quoted text -

Ron,

I tried replacing

myStrings = Array(.....

With

myStrings = Sheets("DataReq").Range("A1:A39")

and got an error "Subscript out of range"

Did I miss something?

Delete rows based on text value in a range
 

See the first macro example on the page
Below the macro you can read how you can add the range option


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Opal" wrote in message ...
On Sep 17, 11:34 am, "Ron de Bruin" wrote:
You can also use a range with the words
Easier to change then

See this tip for the first macro on this pagehttp://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 15, 1:32 pm, "Ron de Bruin" wrote:





The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")

it works, but I wondered if there was a more effective way to achieve
my results.- Hide quoted text -

- Show quoted text -

Ron,

I tried replacing

myStrings = Array(.....

With

myStrings = Sheets("DataReq").Range("A1:A39")

and got an error "Subscript out of range"

Did I miss something?

Delete rows based on text value in a range
 

On Sep 17, 12:38*pm, "Ron de Bruin" wrote:
See the first macro example on the page
Below the macro you can read how you can add the range option

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 17, 11:34 am, "Ron de Bruin" wrote:





You can also use a range with the words
Easier to change then

See this tip for the first macro on this pagehttp://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 15, 1:32 pm, "Ron de Bruin" wrote:

The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")

it works, but I wondered if there was a more effective way to achieve
my results.- Hide quoted text -

- Show quoted text -

Ron,

I tried replacing

myStrings = Array(.....

With

myStrings = Sheets("DataReq").Range("A1:A39")

and got an error "Subscript out of range"

Did I miss something?- Hide quoted text -

- Show quoted text -

Hi Ron,

So I did the following, per your instructions:

If Not IsError(Application.Match(.Value, _
Sheets("DataReq").Range("A1:A39"), 0)) Then .EntireRow.Delete

And when I debug, I get an error:

"Invalid or unqualified reference" pointing to ".Value"

Again, am I missing something?

Delete rows based on text value in a range
 

On Sep 19, 2:31*pm, Opal wrote:
On Sep 17, 12:38*pm, "Ron de Bruin" wrote:





See the first macro example on the page
Below the macro you can read how you can add the range option

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 17, 11:34 am, "Ron de Bruin" wrote:

You can also use a range with the words
Easier to change then

See this tip for the first macro on this pagehttp://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 15, 1:32 pm, "Ron de Bruin" wrote:

The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the text value in column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")

it works, but I wondered if there was a more effective way to achieve
my results.- Hide quoted text -

- Show quoted text -

Ron,

I tried replacing

myStrings = Array(.....

With

myStrings = Sheets("DataReq").Range("A1:A39")

and got an error "Subscript out of range"

Did I miss something?- Hide quoted text -

- Show quoted text -

Hi Ron,

So I did the following, per your instructions:

If Not IsError(Application.Match(.Value, _
Sheets("DataReq").Range("A1:A39"), 0)) Then .EntireRow.Delete

And when I debug, I get an error:

"Invalid or unqualified reference" pointing to ".Value"

Again, am I missing something?- Hide quoted text -

- Show quoted text -

Or should I be using the

"Criteria range on a different sheet" example?

Delete rows based on text value in a range
 

On Sep 19, 3:06*pm, Opal wrote:
On Sep 19, 2:31*pm, Opal wrote:





On Sep 17, 12:38*pm, "Ron de Bruin" wrote:

See the first macro example on the page
Below the macro you can read how you can add the range option

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 17, 11:34 am, "Ron de Bruin" wrote:

You can also use a range with the words
Easier to change then

See this tip for the first macro on this pagehttp://www.rondebruin.nl/delete.htm

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...

On Sep 15, 1:32 pm, "Ron de Bruin" wrote:

The example is working for column A on my page

What are the values that you want to delete in Column A ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Opal" wrote in ...
I have a file that I update weekly and the data source has been
changed to include more data than what I need. I cannot sort
the data from the source so I am trying to create a macro in
excel to remove the extra data I do not need. I only need 14
rows of data, but my source now gives me 55.

I have looked at Ron DeBruin's site for assistance:

http://www.rondebruin.nl/delete.htm#Find

But since I need to remove 41 rows of unneeded data, I was
unsure how to adapt it to my needs. I want to be able to
remove rows based on the textvaluein column A.

Does anyone have any advice?- Hide quoted text -

- Show quoted text -

Hi Ron,

I used your code from your site and typed in all 41 values I want to
delete in this line:

myStrings = Array("Ron", "Dave", "Tom")

it works, but I wondered if there was a more effective way to achieve
my results.- Hide quoted text -

- Show quoted text -

Ron,

I tried replacing

myStrings = Array(.....

With

myStrings = Sheets("DataReq").Range("A1:A39")

and got an error "Subscript out of range"

Did I miss something?- Hide quoted text -

- Show quoted text -

Hi Ron,

So I did the following, per your instructions:

If Not IsError(Application.Match(.Value, _
Sheets("DataReq").Range("A1:A39"), 0)) Then .EntireRow.Delete

And when I debug, I get an error:

"Invalid orunqualifiedreference" pointing to ".Value"

Again, am I missing something?- Hide quoted text -

- Show quoted text -

Or should I be using the

"Criteria range on a different sheet" example?- Hide quoted text -

- Show quoted text -

Ah ha....Criteria range on a different sheet example is what I
needed. It works beautifully!

Thank you!