next empty row

[Ossian13]

Hello everyone,

can anyone tell me the following:
I have a sheet with a header, and my macro is copying rows to the respective sheet but it keeps pasting over my header as i caould not find a solution to move to the next available empty row.

I have tried this but it has no effect

nextrow = Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Row + 1

THank you,
Ossian

 

[Ossian13]

Oh, did not believe it was that simple.
Thanks a lot, really appreciate it!

 

[Ossian13]

Hi @Joe4 again. unfortunately i get a type missmatch error after adding the range ("AO:AT") the macro stopped. I think Range("AO:AT").Find is not the right syntax.

Cheers,
Ossian

 

[Joe4]

I think it doesn't like this:

After:=ActiveCell

part if the ActiveCell is not in the range.

Try this instead:

    Range("AO:AT").Find(What:="Michael", [COLOR=#ff0000]After:=Range("AO1")[/COLOR], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

 

[Ossian13]

Macro copies the same row instead of going to the next one

Hello guys,

im using this code

Sub etc

xLastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To xLastRow
    Columns("AO:AT").Select
    Selection.Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
   ActiveCell.EntireRow.Copy Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
   Next
end sub

The code is much bigger but only this part is the trouble. So, my code looks for the name "Michael" starting from column AO to column AT, and where he finds this string it copies the entire row to another sheet. But the issue is that the macro copies the same row over and over again instead of going to the next one. Could anyone tell me what is the missing part?

Thank you,
Ossian.

 

[Joe4]

Re: Macro copies the same row instead of going to the next one

I have merged your latest question back in to the original thread, since it is an extension of the same problem I have been helping you with.

The general rule of thumb is this:
If the question is a question about a response or code you already have, or is dependent upon the previous response, post it back to the original thread.
If it is a new question that is not dependent upon previous responses, then post it to a new thread.

Back to your question...
- If your test, how many times does "Michael" appear in your range?
- Does it ever appear more than once within the same row?
- Are there just certain columns the name will appear in, or could it be in any of the 6 columns?

 

[Ossian13]

Re: Macro copies the same row instead of going to the next one

Hi, thank you for taking care of my wrongly posted thread and also for the clarifications. I took notice and will be more careful with my posts.
The name appears only once in the same row and it could be anywhere within the 6 columns.

 

[Joe4]

Re: Macro copies the same row instead of going to the next one

OK, try this variation. This should work, assuming that row 1 is a title row, so "Michael" will never exist in row 1.
If it can, please let me know, and we will need to amend the code:

Sub etc()

    Dim xLastRow As Long
    Dim i As Long
    Dim prevCell As Range
    Dim newCell As Range
        
    Application.ScreenUpdating = False
        
    xLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Set prevCell = Range("AO1")
    Set newCell = Range("AO1")
    
    For i = 1 To xLastRow
        Set newCell = Columns("AO:AT").Find(What:="Michael", After:=newCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If newCell.Row > prevCell.Row Then
            newCell.EntireRow.Copy Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Set prevCell = newCell
        Else
            Exit For
        End If
    Next i

    Application.ScreenUpdating = True

End Sub

 

[Joe4]

Re: Macro copies the same row instead of going to the next one

Actually, since we are using the FIND function, we do not actually need to loop through each row. That will cause us to do a lot of unnecessary loop iterations.
Use this instead:

Sub etc()

    Dim xLastRow As Long
    Dim i As Long
    Dim prevCell As Range
    Dim newCell As Range
        
    Application.ScreenUpdating = False
        
    xLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Set prevCell = Range("AO1")
    Set newCell = Range("AO1")
    
    Do
        Set newCell = Columns("AO:AT").Find(What:="Michael", After:=newCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If (newCell.Row > xLastRow) Or (newCell.Row < prevCell.Row) Then
            Exit Do
        Else
            newCell.EntireRow.Copy Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Set prevCell = newCell
        End If
    Loop

    Application.ScreenUpdating = True

End Sub

 

[Ossian13]

Re: Macro copies the same row instead of going to the next one

I would buy you a beer if i ever have the chance to meet you! Thank you very much for your effort and kindness! The code works perfectly, hope someday i will get to help others just as i receive help from you and others here on mrexcel.

Regards,
Ossian.

 

[Joe4]

Re: Macro copies the same row instead of going to the next one

You are welcome!

I am glad we got it all to work out. Hopefully, my logic makes sense.
If you have any questions about it, feel free to ask.