Code:
--------------------

Private Sub CommandButton1_Click()
Dim value, acCell As String
Dim FoundRange As Range
Dim chkEmpty As Integer
Dim aCell As String

value = Sheets("ValidationLists").Range("A1").value

On Error Resume Next
Set FoundRange = Range("A18:F37").Find(What:=value, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Activate
acCell = ActiveCell.Address
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy

'Sheets("Matter Arrising").activate : Problem1


Sheets("Matter Arrising").Range("A2").selct
aCell = Sheets("Matter Arrising").Range(ActiveCell, ActiveCell.Offset(0, 7)).Address
MsgBox "Range are " & aCell

chkEmpty = Sheets("Matter Arrising").Application.CountA(Sheets("Matter Arrising").Range(aCell))
MsgBox "1.) There are " & chkEmpty & " of Records"

If chkEmpty = 0 Then
MsgBox "No Record"
Else
MsgBox "Records Found"
End If

End Sub

--------------------


In My Sheet 1 I have one Button1 to click, then copy after the find
value and paste to sheet2 or Sheets("Matter Arrising"). Before paste to
Sheet2, I have used check empty rows to check whether the rows is empty
or not.

Problem1 :If Sheets("Matter Arrising") is not selected, my checking
emtpy rows is not work. I need user only click the button on sheet1
without going see any sheet2.

Is there any way to check empty rows in Sheet 2 when button1 is clicked
in sheet1?

Rgds
Bernard Ng


--
bernardng
------------------------------------------------------------------------
bernardng's Profile: http://www.excelforum.com/member.php...o&userid=34117
View this thread: http://www.excelforum.com/showthread...hreadid=544212