Copy cells with certain info

[Guest]

I would like to write code to look through a column for a specific word and
if that word is found, select the entire row. Then I would like to loop this
so it goes through a whole group of information. It seems I should be able
to use an If...Then statement to begin with, but then I'm not sure how to
write the code for copying the whole row. Also, is there a better way to do
this besides looping?

 

[Dave Peterson]

Maybe you could apply Data|Filter|autofilter to that column.

Filter by that word (or contains that word) and then copy the visible rows.

If you really need a macro, you could record one when you do it.

(If you have trouble with your recorded macro, post back with your questions.)

 

[Guest]

I actually tried this and the problem I had was that when I recorded the
Macro, it didn't look for just any visible rows. it listed the specific rows
I clicked on. This will not work since the rows will change each time. Is
there a command that specifically looks for only the visible rows left after
the autofilter? I couldn't find anything like that.

Thanks

 

[Dave Peterson]

I'm not sure what you're doing with those visible rows, but you can get to them
via:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myRow As Range

Set myRng = Nothing
With Worksheets("sheet1").AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "no visible cells"
Else
Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With

If myRng Is Nothing Then
'nothing found
Else
For Each myRow In myRng.Rows
MsgBox myRow.Address
Next myRow
End If

End Sub

Lots of people want to copy those visible rows to another worksheet. If that's
what you're heading for:

Option Explicit
Sub testme01a()
Dim myRng As Range
Dim DestCell As Range

Set myRng = Nothing
With Worksheets("sheet1").AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "no visible cells"
Else
Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With


If myRng Is Nothing Then
'nothing found
Else
With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

myRng.Copy _
Destination:=DestCell
End If

End Sub

 

[Guest]

Thank you very much. I'm sure I can use this. I was trying to highlight all
rows where the cell in Column A was blank. Then I wanted to delete those.

Thanks again!!

 

[Dave Peterson]

Sometimes it can be quicker to sort the rows and delete the rows with blanks
when it's one contiguous range.

(just an alternative if you ever have trouble--or want to do it manually.)

Thank you very much. I'm sure I can use this. I was trying to highlight all
rows where the cell in Column A was blank. Then I wanted to delete those.

Thanks again!!