Delete a row if blank cell

[mohd21uk via OfficeKB.com]

I would like to delete a row if the the value in colmn I and H in the
respective row is blank. I would like to know if a macro can do this from the
start to the end of the worksheet.

Thanks.

 

[Guest]

The following macro will work if the data is in Sheet1 and column A has
values in the first row through the last. If Column A is blank it exits the
loop and stops the macro, otherwise it checks the values in columns I and H
and if both are blank it deletes the row.
I've tested this some and it worked okay.

Sub RemoveRows()

Dim wb As Workbook
Dim ws As Worksheet
Dim lngRow As Long
Dim lngRowOffset As Long
Dim intColOffset As Integer
Dim lngCurrentRow As Long
Dim varVal1 As Variant
Dim varVal2 As Variant
Dim varVal3 As Variant
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Range("A1").Select
varVal3 = ActiveCell.Offset(lngRowOffset).Value
Application.ScreenUpdating = False

Do Until varVal3 = ""
varVal1 = ActiveCell.Offset(lngRowOffset, 7).Value
varVal2 = ActiveCell.Offset(lngRowOffset, 8).Value
lngCurrentRow = lngRowOffset + 1
If varVal1 = "" And varVal2 = "" Then
ws.Rows(lngCurrentRow & ":" & lngCurrentRow).Delete
End If
lngRowOffset = lngRowOffset + 1
varVal3 = ActiveCell.Offset(lngRowOffset).Value
Loop

Set wb = Nothing
Set ws = Nothing
Application.ScreenUpdating = True

End Sub