Excel: Test for blank lines 1

[logius]

Could someone help me come up with a method (VBA) for checking to see if there are blank lines between rows of data? Problem is that the number of rows can be variable. All I need to do is run down A1, and if there is a blank line between data, then delete the blank lines. Seems easy, but I can't figure how you can determine if there's blank lines or you've reached the end of the data set. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[RobBroekhuis]

Do you know how many columns of data there are a priori?
Rob

 

[JustinEzequiel]

the UsedRange property may help you

 

[SkipVought]

Hi,
Ues this function to determine the last line in your table. Actually, the function returns TRUE on the first blank row after the last row.

Sub atest()
    For Each cell In Range("A:A")
        cell.Select
        If LastLine Then
            MsgBox cell.Row
            Exit For
        End If
    Next
End Sub
Function LastLine() As Boolean
    With ActiveCell
        If IsEmpty(.Value) Then
            If .End(xlDown).Row = Cells.Rows.Count Then _
                LastLine = True
                Exit Function
            Else
                LastLine = False
        End If
    End With
End Function

Skip,
metzgsk@voughtaircraft.com

 

[logius]

Actually, this isn't what I needed (not sure who gave you the star), but what I ended up doing was invoking the

End(xlDown)

method twice. The first call was at the known begining of the data range and the second was called from that point on. It works in this case because there can only be one gap in the data, but I didn't know how big that gap would be (if were were one at all). ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com