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).Ent ireRow.Delete
columns(1).SpecialCells(xlConstants,xlBlanks).Enti reRow.Delete

--
Regards,
Tom Ogilvy

"Roger" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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).EntireRo w.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" wrote in message
...
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" wrote in message
...
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).EntireRo w.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" wrote in message
...
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.Delet e
On Error goto 0
End Sub

--
Regards,
Tom Ogilvy


"Roger" wrote in message
...
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" wrote in message
...
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).EntireRo w.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" wrote in message
...
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" wrote in message
...
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.Delet e
On Error goto 0
End Sub

--
Regards,
Tom Ogilvy


"Roger" wrote in message
...
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" wrote in message
...
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).EntireRo w.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" wrote in message
...
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