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
