Dear All,

I am faced with a tough situation due to which I am not
able to make a file presentable. For reporting purpose I
have to download files from my office system in excel.
But the problem is that on downloading the system creates
lots of blank rows in the excel file. Now the size of the
download varies every time and also the number of blank
rows between 2 records is not fixed. To counter this I
wish to generate a macro which would identify the blank
rows in the file and delete it automatically.

If anyone has a solution to this kindly let me know the
macro for the same. Thanking you all for the support.

Regards,
Raj

That's the macro code you need mate:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows
(r).Delete
Next r
End Sub

-----Original Message-----
Dear All,

I am faced with a tough situation due to which I am not
able to make a file presentable. For reporting purpose I
have to download files from my office system in excel.
But the problem is that on downloading the system creates
lots of blank rows in the excel file. Now the size of the
download varies every time and also the number of blank
rows between 2 records is not fixed. To counter this I
wish to generate a macro which would identify the blank
rows in the file and delete it automatically.

If anyone has a solution to this kindly let me know the
macro for the same. Thanking you all for the support.

Regards,
Raj

.

Hi

It works fine. Thank you so much for the much needed
support. But would you tell me what does this line mean
actually.
Application.CountA(Rows(r))

Regards

AN alternative without looping

Sub DeleteRows()
Application.ScreenUpdating = False
With ActiveSheet
.Range("A1").EntireRow.Insert
.Range("A1").FormulaR1C1 = "Test"
.Columns("A:A").AutoFilter Field:=1, Criteria1:="="
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
.Rows("1:1").Delete Shift:=xlUp
End With
Application.ScreenUpdating = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Serkan" wrote in message
...
That's the macro code you need mate:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows
(r).Delete
Next r
End Sub

-----Original Message-----
Dear All,

I am faced with a tough situation due to which I am not
able to make a file presentable. For reporting purpose I
have to download files from my office system in excel.
But the problem is that on downloading the system creates
lots of blank rows in the excel file. Now the size of the
download varies every time and also the number of blank
rows between 2 records is not fixed. To counter this I
wish to generate a macro which would identify the blank
rows in the file and delete it automatically.

If anyone has a solution to this kindly let me know the
macro for the same. Thanking you all for the support.

Regards,
Raj

.

It uses the Counta() worksheet function which counts non-
empty cells in VB by adding application. in front of it.
-----Original Message-----
Hi

It works fine. Thank you so much for the much needed
support. But would you tell me what does this line mean
actually.
Application.CountA(Rows(r))

Regards
.

It counts the number of non-blank cells in the row pointed at by the
variable r.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Raj" wrote in message
...
Hi

It works fine. Thank you so much for the much needed
support. But would you tell me what does this line mean
actually.
Application.CountA(Rows(r))

Regards