Macro to delete blank rows In specific range

[yeo_1987]

Hi pals. My spreadsheet has some blank cells in B7:B51. Although they’re blank but they actually contain formula (I turn = 0 to blank).

I understand that there are some ways to delete the rows manually but I need a macro to done it automatically.

Can you guys help me to write the macro with these criteria? Thanks in advanced

 

[Michael M]

Try

Sub MM1()
  With Range("B7:B51")
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[yeo_1987]

Try

Sub MM1()
  With Range("B7:B51")
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

Hi, Michael. First of all thanks for you reply & help.

I ran the macro but ended with run-time error "1004", no cell were found.

I was thinking isn't something to do with my merged column as I merged column B to H into B.

 

[Michael M]

I don't have Excel at the moment, but that would cause problems !
Instead of merging cells, try centering them.......
Highlight the cells>>formta cells>>alignment tab>>Horizontaldropdown>>"center across selection"
Then retry the code.

Merging cells and working with VBA will break your heart !!!

 

[yeo_1987]

I don't have Excel at the moment, but that would cause problems !
Instead of merging cells, try centering them.......
Highlight the cells>>formta cells>>alignment tab>>Horizontaldropdown>>"center across selection"
Then retry the code.

Merging cells and working with VBA will break your heart !!!

Hi, Michael. Thanks again for your reply. I have unmerged the column and re-ran your VBA. Still get error but I found that it works if I delete the formula in column B. In my case the formula in column B is [ =IF(DataSheet!B28=0,"",DataSheet!B28) ]

Kindly advise how to delete the blank rows even they contain formula. Thanks

 

[Michael M]

OK, try this method then

Sub MM2()
Dim i As Long
    For i = 51 To 7 Step -1
        If Range("B" & i).Value = "" Then Rows(i).Delete
    Next i
End Sub

 

[Rick Rothstein]

Although theyÂ’re blank but they actually contain formula

Try

Sub MM1()
  With Range("B7:B51")
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("B").SpecialCells([B][COLOR="#FF0000"]xlConstants[/COLOR][/B], xlErrors).EntireRow.Delete
  End With
End Sub

Michael... please note what I highlighted in red.

 

[Rick Rothstein]

Although theyÂ’re blank but they actually contain formula

What is in the non-blank cells... numbers or text?

Also, please show us the formula you have in the cells.

 

[Michael M]

Gotcha....thanks Rick..
From post #5

=IF(DataSheet!B28=0,"",DataSheet!B28)