Tom
Thanks for that.
I got an error message when i sent the question so didn't think it had even
reached the message group.
I tried your solution and it did what I wanted.
Thanks once again and thanks to Frank also.
Regards
Sean
"Tom Ogilvy" wrote:
What was wrong with yesterdays answer?
On Error Resume Next
columns(4).SpecialCells(xlConstants,xlTextValues). EntireRow.Delete
columns(4).SpecialCells(xlBlanks).EntireRow.Delete
On Error goto 0
If the values are produced by formulas you can use xlFormulas,xlTextValues
if you have error values
xlFormulas,xlErrors
or Logical
xlFormulas,xlLogical
See help on special cells for other options.
--
Regards,
Tom Ogilvy
"Sean" wrote in message
...
Frank
sorry to be a pain but is there not a way that i can avoid the manual side
of this ? My spreadhseet will vary in length and I just want a bit of
code
that will assess all of the active rows and delete those that do not meet
the
criteria.
I had a similar query recently but it revolved around column A. The
coding
is below but I am not sure which parts to change:
'Deletes any rows that do not have numeric value in column A
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf Not IsNumeric(.Cells(Lrow, "A").Value) Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
Thanks
Sean
"Frank Kabel" wrote:
Hi
try the following
- use a helper column (e.g. column F):
- enter the following formula:
=IF(OR(D1="",NOT(ISNUMBER(D1))),"X","")
- copy this down for all rows
- use 'Data - Fiter - autofilter'
- Filter with this column and delete all rows containing 'X' in this
helper
column
- remove the Autofilter
"Sean" wrote:
Frank
I have taken a look at this page and it doesn't really help me. I
have very
little skills in terms of coding so I am hoping for a bespoke response
if at
all possible.
Regards
Sean
"Frank Kabel" wrote:
Hi
see:
http://www.xldynamic.com/source/xld.Deleting.html
"Sean" wrote:
Hi
I would like to delete all rows in my spreadsheet that have a null
or non
numeric value in column D. Anyone know how to go about doing this
?
There is no header row in my spreadsheet.
Thanks in advance.
Sean