Hi,
I need to do a search for a word in some Excel rows eg. "hello" in sheet1.
The word will be contained in rows first cell (so in column A)
When I find word hello I need to copy whole row in sheet2.
Thanks!
Nic
Hi,
I need to do a search for a word in some Excel rows eg. "hello" in sheet1.
The word will be contained in rows first cell (so in column A)
When I find word hello I need to copy whole row in sheet2.
Thanks!
Nic
Off the top
[vba]
On Error Resume Next
Set cell = Columns(1).Find("hello")
ON Error Goto 0
If Not cell Is Nothing Then
cell.EntireRow.Copy Worksheets("Sheet2").Range("A1")
'etc.
End If
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks.
This copies one row only ie. first one from list.
What if I want to copy all rows which contain hello in first cell?
Let's say we have 10 rows starting from A4 to A14.
How do we do that?
Thanks,
Nic
Sheet notices would be your sheet2 and WITHDRAWN would be your hello:
[VBA]Option Explicit
Sub Main()
Dim i As Long
For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
ActiveSheet.Rows(i).Copy Sheets("Notices").Cells(TargetRow(Sheets("Notices"), 1), 1)
' ActiveSheet.Rows(i).Delete
End If
Next i
End Sub
Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
'change targetrow to 2 to move the rows starting on the second row
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Sorry Nic I missed the part about column A.
Change this line:
[VBA]
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
[/VBA]
to
[VBA] If UCase(ActiveSheet.Cells(i, 1).Value) = "WITHDRAWN" Then [/VBA]
for column A
Example attached but be sure to comment out this line if you don't want to remove the rows from the first sheet. It is set up to move the rows not copy them as is:
[VBA]ActiveSheet.Rows(i).Delete[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln