Delete all the blank rows in between

[TANMAY MONDAL]

How can I delete all the blank rows in between or how can I delete all the alternate rows in a selected data sheet.

 

[Bhawani]

As you want only to remove blank rows you'll need to apply filter on columns as per requirement** and to delete entire row*
** - you may use counta, countif or countifs function to count cells lying under your criteria
* - select visible cells via shortcode ( alt with semicolon ( ) or you may use goto by hitting F5

if entire row is empty you have to add a column and then filter it and again remove added column

request you to post sample file for more on this.

 

[Somendra Misra]

Hi Tanmay,

I assume you had alternate rows of data with blank row as complete blanks and data in all column for filled rows. If so you can follow this procedure.

1. Select your entire range of data.
2. Hit F5, Select Special, than select Blanks.
3. Your blank rows will be highlighted.
4. On the Home Tab, Press the Delete dropdown and select Delete Cell, Than Select move cells Up.

That will clear all the blank rows.

For your second query, please post some data.

Regards,

 

[Deepak]

This Will remove all blank rows from active sheet.

Option Explicit

Sub Delete_Blank_Rows()
Dim lrow As Long, i As Long
Application.ScreenUpdating = False
    With ActiveSheet
        lrow = .Cells(Rows.Count, "B").End(xlUp).Row
           If Not lrow > 2 Then Exit Sub
            For i = lrow To 2 Step -1
                If Not Application.CountA(Rows(i)) > 0 Then Rows(i).EntireRow.Delete
            Next
    End With
Application.ScreenUpdating = True
End Sub