Non number cells

[Ben]

I would like to create a macro that will remove an entire
row if in the A colum I have anything else but numbers
(text for example).

 

[Ben...]

I would like to create a macro that will remove an entire
row if in the A colum I have anything else but numbers.
When I say anything else than numbers, I even mean if a
cell is empty, I want to delete the entire row.

The reason is that some rows have nothing in colum "A"
but have other things in other colums. I still want
those colums deleted...

Thanks!!!

 

[Ron de Bruin]

Hi Ben

You can use this that will only keep the rows with a numeric value
in the A column

Sub Example1()
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
End Sub

 

[Ron de Bruin]

Hi Ben I forgot the empty cells

Use this

Sub Example1()
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) Or _
IsEmpty(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[jeff]

HI,

Here's a macro that I believe Frank Kabel posted
a day or so ago - deletes if .value = 0 - modify
for your own situation (.value = "" ???).
(be sure to make a copy of your data first)

jeff



Sub delete_rows()
' delete all rows if they have a zero in col A
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
if .value = 0 then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub