need Macro to delete entire rows that are blank in col B only

[skyport]

need Macro to delete entire rows that are blank in col B only but leave all other rows untouched.

can someone please help me with this?

 

[DushiPunda]

For i = X To 1 Step -1
    If Worksheets("Sheet1").Range("B" & i).Value = "" Then Worksheets("Sheet1").Rows(i).Delete
Next i

Regarding "For i = X to 1" - You'll need to change X to whatever your last row is. Change the 1 to whatever your first row is.

 

[JLGWhiz]

The OP indicates that if column B is blank and another cell in the row is blank then you do not want to delete that row. Delete if column B is the only blank cell in the row. To do that it has to be assumed that all rows are of equal length.

Sub t()
Dim i As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    With ActiveSheet
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
            If Application.CountA(.Cells(i, 1).Resize(, lc)) = lc - 1 Then
               If .Cells(i, 2).Value = "" Then Rows(i).Delete
            End If
        Next
    End With
End Sub

 

[DushiPunda]

The OP indicates that if column B is blank and another cell in the row is blank then you do not want to delete that row. Delete if column B is the only blank cell in the row. To do that it has to be assumed that all rows are of equal length.

Sub t()
Dim i As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    With ActiveSheet
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
            If Application.CountA(.Cells(i, 1).Resize(, lc)) = lc - 1 Then
               If .Cells(i, 2).Value = "" Then Rows(i).Delete
            End If
        Next
    End With
End Sub

JLGWhiz, good call on that. Two changes to your code:

1. .Rows(i).Delete
2. Next i

You were missing the first period for #1, and i for #2.

 

[skyport]

JLGWhiz The idea would be to have it only base the decision to delete based on the contents of column B so if the cell in column B in that row was blank it would delete it. If there was data in it, it would leave the row unchanged

 

[DushiPunda]

JLGWhiz The idea would be to have it only base the decision to delete based on the contents of column B so if the cell in column B in that row was blank it would delete it. If there was data in it, it would leave the row unchanged

If that's the case, my code should suffice. Give it a whirl on a copy of your data.

 

[skyport]

DushiPunda I pasted it exactly as you wrote it into the module and when I ran it, nothing happened I am sorry to report.

 

[JLGWhiz]

JLGWhiz, good call on that. Two changes to your code:

1. .Rows(i).Delete
2. Next i

You were missing the first period for #1, and i for #2.

Good catch on the missing period, but the i is not necessary. The Next will refer to the loop that started it, working from inside out in vba. like So

For i = 1 To 3
    For j = 1 To 4
        For x = 1 To 12
            MsgBox i + j
        Next 'belongs to x
    Next 'belongs to j
Next 'belongs to i

 

[DushiPunda]

Right, well it's not going to work exactly as written. From my post:

Regarding "For i = X to 1" - You'll need to change X to whatever your last row is. Change the 1 to whatever your first row is.

Also, if your Worksheet name is not "Sheet1", then you'll need to update that as well.

Change:
Worksheets("Sheet1")

To:
Worksheets("YourSheetName")

 

[JLGWhiz]

This should do it quickly

Sub t2()
Dim i As Long
    With ActiveSheet
        .Range("B2", .Cells(Rows.Count, 2)).End(xlUp).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub