MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please help with xldown


Posted by Pavan on August 02, 2001 3:36 PM

Hi, I have a list of 2000 numbers in column A and 2000 coressponding titles in column B. Once in a while someone may type leave an empty row all together. I want to catch this error, return an error message, and end sub.

If there is an empty row in row 1 or 2, the code:
Range("A1").End(xlDown).Row
returns 2 or 3 respectively. How can I get excel to return error or 0 if there is no data in A1 (but data in 2 and beyond), or 1 if there is data in A1 and A3 and not A2?

This is really stumping me. If anyone can help me out, it would be appreciated. Thanks,

Pavan


Posted by Ivan F Moala on August 02, 2001 7:13 PM

Try this

Sub Test()
Dim LRwTop As Double
Dim LRwBot As Double
Dim Blk

LRwTop = Range("A1").End(xlDown).Row
LRwBot = Range("A65536").End(xlUp).Row
If LRwTop <> LRwBot Then
MsgBox "Error you have blank cells!" & " between A" & LRwTop & ":" & "A" & LRwBot
Exit Sub
End If
End Sub

If you wanted to select the range irrespective of blanks then this should help;

Sub SelectFirstToLastInColumn()
'Via J Walkenback
Dim TopCell As Range
Dim BottomCell As Range

Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(16384, ActiveCell.Column)

If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = 65536 And BottomCell.Row = 1 Then ActiveCell.Select Else Range(TopCell, BottomCell).Select
End Sub


HTH

Ivan