How to delete uncommon blank lines from 100 worksheets of excel file in fewstep

  • Thread starter Thread starter Dev Shah
  • Start date Start date
D

Dev Shah

Dear Sir,
1. I have my data pasted in 100 worksheets of an excel
file.
2. The data contains no of blank lines in each of the
worksheets.
3. These blank lines are are not in the same sequence in
each of the worksheets.
4. I want to delete these blank lines from all of the
sheets.
5. For that I am trying with active filter , but it work
only when one sheets is selected , it gets deactivated
when more than one sheet is selected.
6. I want to know is there any formula or function or
solution for this kind of problem.
7. I eagerly await for your reply, as the requirement is
urgent.
8. Please help me solving this kind of unusual problem.
 
Hi
have a look at
http://www.cpearson.com/excel/excelM.htm#DeleteBlankRows

and change the given macro to (not fully tested)
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range
Dim wks as worksheet

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For each wks in activeworkbook.worksheets
Set Rng = wks.UsedRange.Rows

N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
next wks

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Back
Top