Excel VBA cutting and pasting rows based on specific conditions

Hello,

This is probably pretty easy for someone not guessing how to make it work:  I am trying to find then move a block of rows if they contain "DPP" in column C using cut and paste.  The rows would then be inserted above a row that contains "GA" in column C.  The data is sorted alphabetically by column C.  

I thought an IF statement something like the following would work, but it's not.

Sub Macro3()
lastrow = Cells(Rows.Count, "c").End(xlUp).Row
    For rwnum = lastrow To 1 Step -1
        With Cells(rwnum, "C")
            If .Value = "DPP" Then
                  Rows(rwnum).EntireRow.Selection.Cut
            If .Value = "GA" Then
            Rows(rwnum).EntireRow.Selection.Paste
            End If
 End Sub


Your help would be appreciated.

Pat
FFNStaffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Have you tried a "helper Column"...

So put a formula such as:
=IF(ISERROR(FIND("DPP",C1)),IF(ISERROR(FIND("GA",C1)),0,2),1)

This will allow you to sort by the helper column as well as column C?

Would this work?
0
Martin LissOlder than dirtCommented:
Try this macro.

Sub MoveDPP()
    Dim rwnum As Long
    Dim lastrow As Long
    Dim intCountDPP As Integer
    Dim rngGA As Range
    Dim rngDPP As Range
    Dim intIndex As Integer
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    lastrow = Cells(Rows.Count, "c").End(xlUp).Row

    ' Find out how many DPP there are
    intCountDPP = Application.CountIf(Range("C1:C" & lastrow), "DPP")
    ' Find out where "GA" is
    Columns("C:C").Select
    With Range("C1:C" & lastrow)
        Set rngGA = .Find(What:="GA", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    End With
    If rngGA Is Nothing Then
        MsgBox "No GA found"
        Exit Sub
    End If
    ' Insert enough blank lines above GA to hold the DPPs
    For intIndex = 1 To intCountDPP
        Rows(rngGA.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
    ' Find the first DPP row
    'Columns("C:C").Select
    With Range("C1:C" & lastrow)
        Set rngDPP = .Find(What:="DPP", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    End With
    ' Copy the DPP rows
    Rows(rngDPP.Row & ":" & rngDPP.Row + intCountDPP - 1).Copy
    ' Paste them to the black rows
    Rows(rngGA.Row - intCountDPP & ":" & rngGA.Row - 1).Select
    ActiveSheet.Paste
    ' Delete the old DPP rows
    Rows(rngDPP.Row & ":" & rngDPP.Row + intCountDPP - 1).Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Set rngDPP = Nothing
    Set rngGA = Nothing

End Sub

Open in new window

0
FFNStaffAuthor Commented:
Hi Bar Man,

I don't think a formula will do what I need.  I'm trying to move the cells as part a whole reformat.  Thank you.

Hi Martin,

Your code worked through counting DPP rows.  There were the correct number of rows highlighted when it stopped but the highlighted rows wouldn't have been the insertion point.  

This is the code that was yellow in the Visual Basic window:

 Rows(rngDPP.Row & ":" & rngDPP.Row + intCountDPP - 1).Copy
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Martin LissOlder than dirtCommented:
Can you attach a spreadsheet that contains the data since my code worked for a sheet I threw together?
0
Martin LissOlder than dirtCommented:
If you can't attach the sheet then maybe you can tell me what's wrong with my data which looked like this in Column C


ccc
DPP
DPP
eee
fff
GA
is
this
xxx


and wound up like this after running the macro

ccc
eee
fff
DPP
DPP
GA
is
this
xxx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
Martin, I had not sorted the data correctly before running your code--my dumb!  It worked perfectly once the data was sorted.  Thank you so much for your help!!!
0
Martin LissOlder than dirtCommented:
If you like you can add this code after line 17 and sort it automatically.

    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("C1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    
    With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
        .SetRange Range("C1:C" & lastrow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window


In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.