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?

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

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" <(E-Mail Removed)> wrote in message news:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
>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?

On Sep 15, 1:32*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> >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.

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" <(E-Mail Removed)> wrote in message news:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> >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.

On Sep 17, 11:34*am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
>
> On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> > >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?

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" <(E-Mail Removed)> wrote in message news:66ed97a6-233d-4309-acef-(E-Mail Removed)...
On Sep 17, 11:34 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
>
> On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> > >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?

On Sep 17, 12:38*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:66ed97a6-233d-4309-acef-(E-Mail Removed)...
>
> On Sep 17, 11:34 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
>
> > On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> > > >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?

On Sep 19, 2:31*pm, Opal <tmwel...@hotmail.com> wrote:
> On Sep 17, 12:38*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:66ed97a6-233d-4309-acef-(E-Mail Removed)...
>
> > On Sep 17, 11:34 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
>
> > > On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> > > > >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?

On Sep 19, 3:06*pm, Opal <tmwel...@hotmail.com> wrote:
> On Sep 19, 2:31*pm, Opal <tmwel...@hotmail.com> wrote:
>
>
>
>
>
> > On Sep 17, 12:38*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:66ed97a6-233d-4309-acef-(E-Mail Removed)...
>
> > > On Sep 17, 11:34 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:0be8f2d8-c50e-43a6-90bd-(E-Mail Removed)...
>
> > > > On Sep 15, 1:32 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tmwel...@hotmail.com> wrote in messagenews:39415e0f-fc51-44dc-a0cc-(E-Mail Removed)...
> > > > > >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!