Hello,

I have seen several variations on this, but can't get them to work on my
spreadsheet. I have several rows of data(around 600, but it will change each
time). How can I delete all rows that contain any data in column J? When I
did the recording macro, it did this:

Selection.AutoFilter Field: =10, Criteria1:="<>"
ActiveWindow.SmallScroll Down:=-12
Selection.EntireRow.Delete

This works for my original data, but when I tested it on prior data, it
didn't work.

My VBA knowledge is very basic, if you can't already tell. :-)
Any help will be appreciated.

Thanks!
Amy

One way:-

Sub mission()
With ActiveSheet
lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
End With
For x = lastrow To 1 Step -1
Cells(x, 10).Select
If ActiveCell.Value <> "" Then
Selection.EntireRow.Delete
End If
Next
End Sub


Mike

"AmyLynn9156" wrote:

> Hello,
>
> I have seen several variations on this, but can't get them to work on my
> spreadsheet. I have several rows of data(around 600, but it will change each
> time). How can I delete all rows that contain any data in column J? When I
> did the recording macro, it did this:
>
> Selection.AutoFilter Field: =10, Criteria1:="<>"
> ActiveWindow.SmallScroll Down:=-12
> Selection.EntireRow.Delete
>
> This works for my original data, but when I tested it on prior data, it
> didn't work.
>
> My VBA knowledge is very basic, if you can't already tell. :-)
> Any help will be appreciated.
>
> Thanks!
> Amy

Thanks! That worked. Is there any way to make this move faster? It took a
good 5 minutes for this to delete all of the rows.

"Mike H" wrote:

> One way:-
>
> Sub mission()
> With ActiveSheet
> lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
> End With
> For x = lastrow To 1 Step -1
> Cells(x, 10).Select
> If ActiveCell.Value <> "" Then
> Selection.EntireRow.Delete
> End If
> Next
> End Sub
>
>
> Mike
>
> "AmyLynn9156" wrote:
>
> > Hello,
> >
> > I have seen several variations on this, but can't get them to work on my
> > spreadsheet. I have several rows of data(around 600, but it will change each
> > time). How can I delete all rows that contain any data in column J? When I
> > did the recording macro, it did this:
> >
> > Selection.AutoFilter Field: =10, Criteria1:="<>"
> > ActiveWindow.SmallScroll Down:=-12
> > Selection.EntireRow.Delete
> >
> > This works for my original data, but when I tested it on prior data, it
> > didn't work.
> >
> > My VBA knowledge is very basic, if you can't already tell. :-)
> > Any help will be appreciated.
> >
> > Thanks!
> > Amy

Check out this page for a few ways
http://www.rondebruin.nl/delete.htm


--

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


"AmyLynn9156" <(E-Mail Removed)> wrote in message news:0100382D-8232-4016-B037-(E-Mail Removed)...
> Thanks! That worked. Is there any way to make this move faster? It took a
> good 5 minutes for this to delete all of the rows.
>
> "Mike H" wrote:
>
>> One way:-
>>
>> Sub mission()
>> With ActiveSheet
>> lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
>> End With
>> For x = lastrow To 1 Step -1
>> Cells(x, 10).Select
>> If ActiveCell.Value <> "" Then
>> Selection.EntireRow.Delete
>> End If
>> Next
>> End Sub
>>
>>
>> Mike
>>
>> "AmyLynn9156" wrote:
>>
>> > Hello,
>> >
>> > I have seen several variations on this, but can't get them to work on my
>> > spreadsheet. I have several rows of data(around 600, but it will change each
>> > time). How can I delete all rows that contain any data in column J? When I
>> > did the recording macro, it did this:
>> >
>> > Selection.AutoFilter Field: =10, Criteria1:="<>"
>> > ActiveWindow.SmallScroll Down:=-12
>> > Selection.EntireRow.Delete
>> >
>> > This works for my original data, but when I tested it on prior data, it
>> > didn't work.
>> >
>> > My VBA knowledge is very basic, if you can't already tell. :-)
>> > Any help will be appreciated.
>> >
>> > Thanks!
>> > Amy