PDA

View Full Version : [SOLVED] copy entire row to another sheet if cloumn A contains numeric data on Cloumn A



parscon
09-03-2013, 10:52 AM
Dear Friend,
I need a VBA Code that check the column A on Sheet 1 and if find numeric data (numbers like 1-2-3 ....) on column A copy their row to sheet 2 .
Thank you very much.

raj85
09-05-2013, 01:49 AM
Use below code

Sub test()
Dim iRNum As Integer, temp As Integer, iRNum2 As Integer
iRNum = 2
iRNum2 = 2
While Sheet1.Range("A" & iRNum).Value <> vbEmpty
If IsNumeric(Sheet1.Range("A" & iRNum).Value) Then
Sheet1.Range("A" & iRNum).EntireRow.Copy Sheet2.Range("A" & iRNum2)
iRNum2 = iRNum2 + 1
End If
iRNum = iRNum + 1
Wend
End Sub

parscon
09-05-2013, 03:05 AM
Thank you , but code does not work , for example my data are not ordered and consecutive , that mean I have also blank row between my data on row A . and after blank row this code will not copy my other data .
And Also the speed of running code in slow. (it is not so important)

Thank you again.

raj85
09-05-2013, 03:23 AM
Use this one



Sub test()
Dim iRNum As Integer, temp As Integer, iRNum2 As Integer, LastRow As Long
Debug.Print Now()
LastRow = Sheet1.Range("A& Rows.Count).End(xlUp).Row
iRNum = 2
iRNum2 = 2
While iRNum <= LastRow
If IsNumeric(Sheet1.Range("A" & iRNum).Value) And Sheet1.Range("A" & iRNum).Value <> vbEmpty Then
Sheet1.Range("A" & iRNum).EntireRow.Copy Sheet2.Range("A" & iRNum2)
iRNum2 = iRNum2 + 1
End If
iRNum = iRNum + 1
Wend
Debug.Print Now()
End Sub

And it working faster I have tested it on 5000 rows of data where it copied 3500 rows on sheet2 in 4 seconds.

parscon
09-05-2013, 03:31 AM
Thank you , it is done,

Thanks for your big help .

raj85
09-05-2013, 04:04 AM
You are welcome.
Please mark this thread as Solved