Deleting a row of data based upon the value in column D

[Guest]

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

 

[Guest]

Hi
see:
http://www.xldynamic.com/source/xld.Deleting.html

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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