Macro that deletes certain rows and not others

I would like to test the following which are in about 15 rows

if cell contains any kind of text then go to next row as don't want to
delete rows
with text in them
exit if
else - this covers blanks cells and cells with numbers in them
delete row

Repeat until done about 15 times

Any suggestions would be greatly appreciated.

Roger

Try something like this (save your data before testing). This tests each used
cell in Column A and deletes the entire row if that cell is empty or contains
numeric data.

Sub DelRows()

Dim endRow As Long
Dim counter As Long

endRow = Cells(Rows.Count, 1).End(xlUp).Row

For counter = endRow To 1 Step -1
If IsNumeric(Cells(counter, 1).Value) Or _
IsEmpty(Cells(counter, 1).Value) Then
Cells(counter, 1).EntireRow.Delete
End If
Next counter

End Sub

Hope this helps
Rowan

"Roger" wrote:

> I would like to test the following which are in about 15 rows
>
> if cell contains any kind of text then go to next row as don't want to
> delete rows
> with text in them
> exit if
> else - this covers blanks cells and cells with numbers in them
> delete row
>
> Repeat until done about 15 times
>
> Any suggestions would be greatly appreciated.
>
> Roger
>
>
>

columns(1).SpecialCells(xlConstants,xlNumbers).EntireRow.Delete
columns(1).SpecialCells(xlConstants,xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Roger" <[email protected]> wrote in message
news:[email protected]...
> I would like to test the following which are in about 15 rows
>
> if cell contains any kind of text then go to next row as don't want to
> delete rows
> with text in them
> exit if
> else - this covers blanks cells and cells with numbers in them
> delete row
>
> Repeat until done about 15 times
>
> Any suggestions would be greatly appreciated.
>
> Roger
>
>

If you want a good answer, you have to take the trouble to write a good
question.

How many populated columns are there in each row?

Do you want to delete the row only if NONE of the cells in that row contains
text?

If so, try the following:

Sub DeleteSelectedRows(rng As Range)
Dim i As Integer, j As Integer, fDelete As Boolean
fDelete = True
For i = rng.Rows.Count To 1 Step -1
For j = 1 To rng.Columns.Count
If WorksheetFunction.IsText(rng(i, j)) Then
fDelete = False
Exit For
End If
Next
If fDelete Then rng(i, j).EntireRow.Delete
fDelete = True
Next
End Sub

--

Vasant


"Roger" <[email protected]> wrote in message
news:[email protected]...
> I would like to test the following which are in about 15 rows
>
> if cell contains any kind of text then go to next row as don't want to
> delete rows
> with text in them
> exit if
> else - this covers blanks cells and cells with numbers in them
> delete row
>
> Repeat until done about 15 times
>
> Any suggestions would be greatly appreciated.
>
> Roger
>
>

First of all let me thank each of you Rowan, Tom and Vasant for trying to
solve my problem. Unfortunately because I am a relatively basic macro user
I could not get any of the 3 macros to work although I did spend several
hours trying.

However I have learned a lot so here are my ideas that may help you solve
the problem if you would be so kind. My data starts on row 14 and is spread
out down to row 50 before any corrections are done. They do end up as 15 as
previously mentioned. The column to search is D where there are only cells
that are either blank, numeric or text. Column E has my data in it and does
not need any work that won't be solved by the correct info in column C.

I look forward to your answers and thank you again for all your help.

Roger


"Roger" <[email protected]> wrote in message
news:[email protected]...
>I would like to test the following which are in about 15 rows
>
> if cell contains any kind of text then go to next row as don't want to
> delete rows
> with text in them
> exit if
> else - this covers blanks cells and cells with numbers in them
> delete row
>
> Repeat until done about 15 times
>
> Any suggestions would be greatly appreciated.
>
> Roger
>

Roger

I am afraid you new explanation has left me more confused than before but
that could just be me

If you just want to delete any row which has a blank or numeric data in
column D then you can adapt Tom's answer as follows:

Sub DelRows()
On Error Resume Next
Columns(4).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

If there is something else you want to achieve then you will probably need
to explain what is in rows 1 - 14 and what you want done with these rows,
what data is in columns E and C and what their relationship is to column D
and what you mean by "before corrections are done".

Hope this helps
Rowan

"Roger" wrote:

>
> First of all let me thank each of you Rowan, Tom and Vasant for trying to
> solve my problem. Unfortunately because I am a relatively basic macro user
> I could not get any of the 3 macros to work although I did spend several
> hours trying.
>
> However I have learned a lot so here are my ideas that may help you solve
> the problem if you would be so kind. My data starts on row 14 and is spread
> out down to row 50 before any corrections are done. They do end up as 15 as
> previously mentioned. The column to search is D where there are only cells
> that are either blank, numeric or text. Column E has my data in it and does
> not need any work that won't be solved by the correct info in column C.
>
> I look forward to your answers and thank you again for all your help.
>
> Roger
>
>
> "Roger" <[email protected]> wrote in message
> news:[email protected]...
> >I would like to test the following which are in about 15 rows
> >
> > if cell contains any kind of text then go to next row as don't want to
> > delete rows
> > with text in them
> > exit if
> > else - this covers blanks cells and cells with numbers in them
> > delete row
> >
> > Repeat until done about 15 times
> >
> > Any suggestions would be greatly appreciated.
> >
> > Roger
> >
>
>
>

Rowan, thanks for the update and sorry for any confusion. Your new macro
works very well with one exception - hopefully minor.
I would like the data to start at D16 as there is info above there that I
would like to keep there if possible.

BTW I am using Excel 2002 and would like it to work in Excel 5 as well if
that is possible.

Thanks,

Roger


"Rowan" <[email protected]> wrote in message
news:[email protected]...
> Roger
>
> I am afraid you new explanation has left me more confused than before but
> that could just be me
>
> If you just want to delete any row which has a blank or numeric data in
> column D then you can adapt Tom's answer as follows:
>
> Sub DelRows()
> On Error Resume Next
> Columns(4).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
> Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub
>
> If there is something else you want to achieve then you will probably need
> to explain what is in rows 1 - 14 and what you want done with these rows,
> what data is in columns E and C and what their relationship is to column D
> and what you mean by "before corrections are done".
>
> Hope this helps
> Rowan
>
> "Roger" wrote:
>
>>
>> First of all let me thank each of you Rowan, Tom and Vasant for trying to
>> solve my problem. Unfortunately because I am a relatively basic macro
>> user
>> I could not get any of the 3 macros to work although I did spend several
>> hours trying.
>>
>> However I have learned a lot so here are my ideas that may help you solve
>> the problem if you would be so kind. My data starts on row 14 and is
>> spread
>> out down to row 50 before any corrections are done. They do end up as 15
>> as
>> previously mentioned. The column to search is D where there are only
>> cells
>> that are either blank, numeric or text. Column E has my data in it and
>> does
>> not need any work that won't be solved by the correct info in column C.
>>
>> I look forward to your answers and thank you again for all your help.
>>
>> Roger
>>
>>
>> "Roger" <[email protected]> wrote in message
>> news:[email protected]...
>> >I would like to test the following which are in about 15 rows
>> >
>> > if cell contains any kind of text then go to next row as don't want to
>> > delete rows
>> > with text in them
>> > exit if
>> > else - this covers blanks cells and cells with numbers in them
>> > delete row
>> >
>> > Repeat until done about 15 times
>> >
>> > Any suggestions would be greatly appreciated.
>> >
>> > Roger
>> >
>>
>>
>>

Sub DelRows()
Dim rng as Range
On Error Resume Next
set rng = Range("D16",Cells(rows.count,4).End(xlup))
On Error Resume Next
rng.SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error goto 0
End Sub

--
Regards,
Tom Ogilvy


"Roger" <[email protected]> wrote in message
news:%[email protected]...
> Rowan, thanks for the update and sorry for any confusion. Your new macro
> works very well with one exception - hopefully minor.
> I would like the data to start at D16 as there is info above there that I
> would like to keep there if possible.
>
> BTW I am using Excel 2002 and would like it to work in Excel 5 as well if
> that is possible.
>
> Thanks,
>
> Roger
>
>
> "Rowan" <[email protected]> wrote in message
> news:[email protected]...
> > Roger
> >
> > I am afraid you new explanation has left me more confused than before
but
> > that could just be me
> >
> > If you just want to delete any row which has a blank or numeric data in
> > column D then you can adapt Tom's answer as follows:
> >
> > Sub DelRows()
> > On Error Resume Next
> > Columns(4).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
> > Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > End Sub
> >
> > If there is something else you want to achieve then you will probably
need
> > to explain what is in rows 1 - 14 and what you want done with these
rows,
> > what data is in columns E and C and what their relationship is to column
D
> > and what you mean by "before corrections are done".
> >
> > Hope this helps
> > Rowan
> >
> > "Roger" wrote:
> >
> >>
> >> First of all let me thank each of you Rowan, Tom and Vasant for trying
to
> >> solve my problem. Unfortunately because I am a relatively basic macro
> >> user
> >> I could not get any of the 3 macros to work although I did spend
several
> >> hours trying.
> >>
> >> However I have learned a lot so here are my ideas that may help you
solve
> >> the problem if you would be so kind. My data starts on row 14 and is
> >> spread
> >> out down to row 50 before any corrections are done. They do end up as
15
> >> as
> >> previously mentioned. The column to search is D where there are only
> >> cells
> >> that are either blank, numeric or text. Column E has my data in it and
> >> does
> >> not need any work that won't be solved by the correct info in column C.
> >>
> >> I look forward to your answers and thank you again for all your help.
> >>
> >> Roger
> >>
> >>
> >> "Roger" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I would like to test the following which are in about 15 rows
> >> >
> >> > if cell contains any kind of text then go to next row as don't want
to
> >> > delete rows
> >> > with text in them
> >> > exit if
> >> > else - this covers blanks cells and cells with numbers in them
> >> > delete row
> >> >
> >> > Repeat until done about 15 times
> >> >
> >> > Any suggestions would be greatly appreciated.
> >> >
> >> > Roger
> >> >
> >>
> >>
> >>
>
>

Say you folks have been a wonderful help. Thanks ever so much for
persevering with an amateur. Without you I would never have got it right.

Many thanks,

Roger


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Sub DelRows()
> Dim rng as Range
> On Error Resume Next
> set rng = Range("D16",Cells(rows.count,4).End(xlup))
> On Error Resume Next
> rng.SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
> rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> On Error goto 0
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Roger" <[email protected]> wrote in message
> news:%[email protected]...
>> Rowan, thanks for the update and sorry for any confusion. Your new macro
>> works very well with one exception - hopefully minor.
>> I would like the data to start at D16 as there is info above there that I
>> would like to keep there if possible.
>>
>> BTW I am using Excel 2002 and would like it to work in Excel 5 as well if
>> that is possible.
>>
>> Thanks,
>>
>> Roger
>>
>>
>> "Rowan" <[email protected]> wrote in message
>> news:[email protected]...
>> > Roger
>> >
>> > I am afraid you new explanation has left me more confused than before
> but
>> > that could just be me
>> >
>> > If you just want to delete any row which has a blank or numeric data in
>> > column D then you can adapt Tom's answer as follows:
>> >
>> > Sub DelRows()
>> > On Error Resume Next
>> > Columns(4).SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
>> > Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>> > End Sub
>> >
>> > If there is something else you want to achieve then you will probably
> need
>> > to explain what is in rows 1 - 14 and what you want done with these
> rows,
>> > what data is in columns E and C and what their relationship is to
>> > column
> D
>> > and what you mean by "before corrections are done".
>> >
>> > Hope this helps
>> > Rowan
>> >
>> > "Roger" wrote:
>> >
>> >>
>> >> First of all let me thank each of you Rowan, Tom and Vasant for trying
> to
>> >> solve my problem. Unfortunately because I am a relatively basic macro
>> >> user
>> >> I could not get any of the 3 macros to work although I did spend
> several
>> >> hours trying.
>> >>
>> >> However I have learned a lot so here are my ideas that may help you
> solve
>> >> the problem if you would be so kind. My data starts on row 14 and is
>> >> spread
>> >> out down to row 50 before any corrections are done. They do end up as
> 15
>> >> as
>> >> previously mentioned. The column to search is D where there are only
>> >> cells
>> >> that are either blank, numeric or text. Column E has my data in it
>> >> and
>> >> does
>> >> not need any work that won't be solved by the correct info in column
>> >> C.
>> >>
>> >> I look forward to your answers and thank you again for all your help.
>> >>
>> >> Roger
>> >>
>> >>
>> >> "Roger" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I would like to test the following which are in about 15 rows
>> >> >
>> >> > if cell contains any kind of text then go to next row as don't want
> to
>> >> > delete rows
>> >> > with text in them
>> >> > exit if
>> >> > else - this covers blanks cells and cells with numbers in them
>> >> > delete row
>> >> >
>> >> > Repeat until done about 15 times
>> >> >
>> >> > Any suggestions would be greatly appreciated.
>> >> >
>> >> > Roger
>> >> >
>> >>
>> >>
>> >>
>>
>>
>
>