Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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.) Dawn wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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" wrote: 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.) Dawn wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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 Dawn wrote: 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" wrote: 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.) Dawn wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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" wrote: 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 Dawn wrote: 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" wrote: 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.) Dawn wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells with certain info
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.) Dawn wrote: 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" wrote: 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 Dawn wrote: 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" wrote: 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.) Dawn wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a cell's value to copy another cells info to another workshe | Excel Worksheet Functions | |||
Move cell info and info in range of cells on new entry | Excel Discussion (Misc queries) | |||
How to copy info into blank cells within a column (going up the co | Excel Discussion (Misc queries) | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
comparing a column of cell and then copy info to other cells | Excel Discussion (Misc queries) |