Excel Macro for moving whole row according to column value

[ayk5473]

Hi,I am very new to macros and in need of solution please. So I have over 140,000 rows of data in which column "C" has specific keyword(s). I would like to create a macro in which find if it does contain these keywords it would copy it to another spreadsheet.

Example: Column C might say
-Oregon State
-Philadelphia City
-Detroit City
-New York State


I would like to search for keywords such as "state" or "city" and once it does find it have the whole row move over to another sheet. I would really appreciate all the help. Thank you

 

[alansidman]

try this:

Option Explicit


Sub CityState()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("Sheet1")    'change name if necessary
    Set s2 = Sheets("Sheet2")    'change name if necessary
    lr = s1.Range("C" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("C" & i), "City",1) > 0 Or InStr(s1.Range("C" & i), "State",1) > 0 Then
            s1.Range("C" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"




End Sub

 

[ayk5473]

try this:

Option Explicit


Sub CityState()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("Sheet1")    'change name if necessary
    Set s2 = Sheets("Sheet2")    'change name if necessary
    lr = s1.Range("C" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("C" & i), "City",1) > 0 Or InStr(s1.Range("C" & i), "State",1) > 0 Then
            s1.Range("C" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"




End Sub

I have tried and it seems to be moving over only the specific column that matches the search. I kind of want the whole row to move over if it matches the column. Is this possible

 

[alansidman]

ok. I will change the code, but if you re-read your first post, that was not indicated.

change this line of code

s1.Range("C" & i).Copy s2.Range("A" & lr2 + 1)

to

s1.Range("C" & i).entireRow.Copy s2.Range("A" & lr2 + 1)</pre>

 

[ayk5473]

ok. I will change the code, but if you re-read your first post, that was not indicated.

change this line of code

s1.Range("C" & i).Copy s2.Range("A" & lr2 + 1)

to

s1.Range("C" & i).entireRow.Copy s2.Range("A" & lr2 + 1)

Perfect, thank you and do you recommend any place to learn macros well, you seem very knowledgably in the area.

 

[alansidman]

Buy one of Mr Excels Macro Books. Google is your friend. This forum and others of the same type are a good place to try and learn.