Delete rows with blank cells, do not check column A

[moium]

Hi there,
I have a worksheet with 'Record' in row 1 and variable names in column A. I need to write a macro that will delete all empty rows. The check should begin from column B, not checking column A. The current code I have (below the table) only checks column B and if the cell is blank, it's respective row is deleted. So in my poor representation of an Excel spreadsheet below, only row 3 would be deleted.

A B C D E F G ....
1 var rcd1 rcd2 rcd3 rcd4 rcd5 rcd6 ....
2 x 0215 0225
3 y
4 z 10 20 30

current code works well but for only a specific column
On Error Resume Next ' In case there are no blanks
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97

Thanks for any help provided.

Mark

[Bob Phillips]

How about

[vba]

Dim LastCol As Long

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
On Error Resume Next ' In case there are no blanks
Columns("B:B").Resize(, LastCol - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97
[/vba]

[moium]

Thank you xld,
Unfortunately the code does not work. It only goes through once and does not delete any rows. I'm not exactly sure what the addition of the resize function does. Any other ideas?
Mark

[moium]

I figured out a solution. I iterate through the valid cells in the row, and then through the rows. If the valid cells contain no data then the row is deleted.

[VBA]
Sub deleteEmptyRows()

Dim lastCol As Long
Dim lastRow As Long
Dim data As Integer

'LastCol=4 if 3 records
'does not check cells in row 1, starts @ row 2
lastCol = Cells(2, Columns.Count).End(xlToLeft).Column
lastRow = ActiveSheet.UsedRange.Rows.Count

For i = lastRow To 2 Step -1 'start @ lastRow, end @ row 2
data = 0
For x = 2 To lastCol
If Len(Cells(i, x)) > 0 Then 'cell not blank, Cells(6,1) is cell A6
data = data + 1
End If
Next x

If data = 0 Then
Rows(i).Delete
End If

Next i

MsgBox "Done"

End Sub
[/VBA]