Identifying blank rows

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

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
 
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.Delete
.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)
 
It uses the Counta() worksheet function which counts non-
empty cells in VB by adding application. in front of it.
 
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)
 
Back
Top