Deleting lines with no data

  • Thread starter Thread starter =?iso-8859-1?Q?Paulo_Gon=E7alves?=
  • Start date Start date
?

=?iso-8859-1?Q?Paulo_Gon=E7alves?=

I'm a newbie in excel and i want to now how can i delete a
full line in excel that have no data on it, From line 1 to
400 (Supose) i'm trying to make a code that choose all the
lines that have no data and automatic select and delete
them.

Help should be apreciated....Thank you!!
 
Something like (untested):

Sub DeleteBlankRows()
Dim i As Long, rng As Range
For i = 1 To 400
If WorksheetFunction.CountA(Rows(i)) = 0 Then
If rng Is Nothing then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next
rng.Delete
End Sub
 
Vasant's approach will work perfectly well for 99% of cases in a simple
book. If you are looking for a more complex solution there's a routine in my
XspandXL add-in on the site below that does this for rows and or columns
while also checking for charts and other shapes, and dealing with any hidden
rows or outlining in the sheet.

Robin Hammond
www.enhanceddatasystems.com
 
Back
Top