how to write to macro to only delete rows under certain conditions

[cazaril]

Hi there,

I need to delete rows of data which the last column in the range is either a
blank or a non-integer. If the last column contains a integer, it does not
delete that particular row

How do I do that?

example given below:

A1 1.3233
A2
A3 6
A4 0.823
A5
A6 9

 

[Bernie Deitrick]

cazaril,

Assuming that your have a block of data that starts in A1 (all the columns are adjacent, with no
fully blank columns or rows interrupting the table), this macro should do what you want.

HTH,
Bernie
MS Excel MVP

Sub DeleteRows()
Dim myRows As Long
Dim myCol As Integer
Range("A1").EntireColumn.Insert
myRows = Range("B65536").End(xlUp).Row
myCol = Range("B65536").End(xlUp).CurrentRegion.Columns.Count

Range("A1").Value = "Delete Row criteria"
Range("A2:A" & myRows).FormulaR1C1 = _
"=IF(OR(RC[" & myCol & "]<>INT(RC[" & myCol & _
"]),RC[" & myCol & "]=""""),""SortLow"",""SortHigh"")"
With Range("A:A")
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="SortLow", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

 

[barnabel]

I am going to assume that you have the data you are interested in selected.
You could easily define a range to use instead of the selection range I am
using.

Sub delRows()
Dim currRow As Long

For currRow = Selection.Row + Selection.Rows.Count - 1 To Selection.Row Step
-1
If (IsEmpty(Cells(currRow, Selection.Column + Selection.Columns.Count -
1)) Or _
Cells(currRow, Selection.Column + Selection.Columns.Count - 1) <>
Int(Cells(currRow, Selection.Column + Selection.Columns.Count - 1))) Then
Rows(currRow).Delete Shift:=xlUp
End If
Next
End Sub