PDA

View Full Version : Solved: vba delete entire row



benong
08-19-2010, 03:00 AM
hi,
i'm having problem with vba coding, pls help.

Row 1 contains table heading.
Data starts from row 2 .......onwards
column A data contains NO blank cell in between rows.
column J data contains the department name, but with blank cell in between rows.

i want to go through this list of data, delete the entire row whose value in column J is NOT equal to "MOULD", "FG" and "FIN".

I'm having problems with my code, can someone guide me, thanks.



Sub formatList()
'delete unwanted rows
Range("A2").Select
currentCellPosition = 2

Do Until IsEmpty(ActiveCell)
goToCell = "J" & currentCellPosition
Range(goToCell).Select

If ActiveCell.Value <> "MOULD" _
Or ActiveCell.Value <> "FG" _
Or ActiveCell.Value <> "FIN" Then
ActiveCell.EntireRow.Delete
End If

backToFirstCell = "A" & currentCellPosition
Range(backToFirstCell).Select
ActiveCell.Offset(1, 0).Select
currentCellPosition = currentCellPosition + 1
Loop
End Sub

Bob Phillips
08-19-2010, 03:22 AM
Sub formatList()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = LastRow To 2 Step -1

If .Cells(i, "J").Value <> "MOULD" _
And .Cells(i, "J").Value <> "FG" _
And .Cells(i, "J").Value <> "FIN" Then
.Rows(i).Delete
End If
Next i
End With
End Sub

benong
08-19-2010, 05:09 PM
Dear xId, many thanks for your guidance :)