Removing blank rows other than manually

[Uzzal]

Hi !
is there any way to remove all the blank rows from a huge data sheet other than doing it one by one manually? I have a work sheet of around 600 pages and feeling drowsy!!

 

[Somendra Misra]

Hi Uzzal,

If the entire rows are blanks and rows in which there is data has data in all the columns, you can try GoTo dialogue box:

Select your entire data.
Press F5 and in the GoTo Dialogue box select Special.
Than select Blanks and press OK.
This will highlight all the rows with blanks. Now Select Delete Rows option from the ribbon.

Regards,

 

[Uzzal]

Thank you ...........what should I say Boss ? Yaar ? Brother ? any thing that suits you, You are great.

 

[Deepak]

You may try this too...

Option Explicit

Sub Delete_Blank_Rows_WS()
Dim lrow As Long, i As Long, ws As Worksheet, Aws As Worksheet

Application.ScreenUpdating = False
Set Aws = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    With ActiveSheet
        lrow = .UsedRange.Rows.Count
            For i = lrow To 1 Step -1
                If Not Application.CountA(Rows(i)) > 0 Then _
                    Rows(i).EntireRow.Delete
            Next
    End With
Next
Aws.Activate
Set Aws = Nothing
Application.ScreenUpdating = True
End Sub

 

[Somendra Misra]

@Uzzal

Just call me Somendra. Glad if the suggestion helped you.

Regards,

 

[Uzzal]

You may try this too...

Option Explicit

Sub Delete_Blank_Rows_WS()
Dim lrow As Long, i As Long, ws As Worksheet, Aws As Worksheet

Application.ScreenUpdating = False
Set Aws = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    With ActiveSheet
        lrow = .UsedRange.Rows.Count
            For i = lrow To 1 Step -1
                If Not Application.CountA(Rows(i)) > 0 Then _
                    Rows(i).EntireRow.Delete
            Next
    End With
Next
Aws.Activate
Set Aws = Nothing
Application.ScreenUpdating = True
End Sub

Thanks Deepak, you are so generous

 

[Deepak]

Hi Uzzal,
Welcome dear......