Moving Entire Row at the bottom of the page.

[TryingBest]

Hi,
I am new to this amazing forum where great help is offered.
I wanted to move an entire row at the bottom of the page based on a specific word/criteria match in a column. I found this piece of code and implemented it, which works great.
But, running into an issue, if the word is not matched it gives a debug error - rather than moving on to the next search item in the list.

I am very much new to this coding/VBA, so asking for help. Code is pasted below.
If 'abc' is not in column C, it gives an error and not moving on to the next item 'xyz'

Sub MoveRows()
   Dim rng As Range
   With Range("C:C")
      .Replace "abc", "=Xabx", xlWhole, , False, , False, False
      Set rng = .SpecialCells(xlFormulas, xlErrors)
      .Replace "=Xabc", "abc", xlWhole, , False, , False, False
      rng.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(2)
      rng.EntireRow.Delete
   End With
  
   With Range("C:C")
      .Replace "xyz", "=Xxyz", xlWhole, , False, , False, False
      Set rng = .SpecialCells(xlFormulas, xlErrors)
      .Replace "=Xxyz", "xyz", xlWhole, , False, , False, False
      rng.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(2)
      rng.EntireRow.Delete
   End With
  
End Sub

 

[RobP]

Hi,

the function is outside the main Sub(). You don't integrate it inside. It gets called as a function (like a subroutine) from within your main code, then jump out to check if your sheetname actually exists first.

From your comments, something doesn't stack up, as if this function wasn't called and working - it would not create your new sheet ?

Try the function outside your main SUB, as I have it above.

cheers
Rob

 

[TryingBest]

Hi Rob,

Sorry for the late reply. I tried this code but doesn't work. It creates the new Tab "myNewSheet" but, then the code stops functioning there.
Also, Please share how to incorporate this private function in the main SUB, as this returns an error.

Hi.
Sorry, I apologize. I was searching for the wrong term in the code, and I thought the code is not working
Thank you so much for the help.

One more thing to ask, I haven't searched it, so just asking. What if I want to cut-paste the desired rows in the given active sheet only and not to another sheet?
What could be done?

 

[RobP]

Hi

In this case, you would have succeeded to go full circle, right back to the original code you had at the start of this post - which as far as I can tell seemed to do just that ?

Rgds
Rob

 

[TryingBest]

Hi

In this case, you would have succeeded to go full circle, right back to the original code you had at the start of this post - which as far as I can tell seemed to do just that ?

Rgds
Rob

Hi Rob,

Yes, that initial code works fine, but for some reason, it doesn't seem to search the required term in Column A - though Column A is in text format.
Is it possible to alter this code, so rows can be copied in the same worksheet - at the end of the page with an offset of 2 rows.

I tried with this, but this kind of created a loop in the working sheet.
" .Copy Cells(Rows.Count, "A").End(xlUp)(2)"


EndRw = Cells(Rows.Count, "A").End(xlUp).row
SrchTerm = "xyz"
For Rw = 2 To EndRw
If InStr(Cells(Rw, "A").Value, SrchTerm) Then
With Cells(Rw, "A").Resize(, 18)
.Copy Sheets("today").Cells(Rows.Count, "A").End(xlUp)(2)
.Delete Shift:=xlUp
End With
Rw = Rw - 1
End If
Next Rw

 

[RobP]

Hi,

hard to see why the original code doesn't work any more (as you said it worked like a charm before..), so maybe something in your data.

To get it to search column A (it currently searches column C), just change the C:C with A:A instead ?

Sub MoveRows()
Dim rng As Range
With Range("C:C")
.Replace "abc", "=Xabx", xlWhole, , False, , False, False
On Error Resume Next
Set rng = .SpecialCells(xlFormulas, xlErrors)
    If Not rng Is Nothing Then
        .Replace "=Xabc", "abc", xlWhole, , False, , False, False
        rng.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(2)
        rng.EntireRow.Delete
    End If
End With

 

[TryingBest]

Hi,

hard to see why the original code doesn't work any more (as you said it worked like a charm before..), so maybe something in your data.

To get it to search column A (it currently searches column C), just change the C:C with A:A instead ?

Sub MoveRows()
Dim rng As Range
With Range("C:C")
.Replace "abc", "=Xabx", xlWhole, , False, , False, False
On Error Resume Next
Set rng = .SpecialCells(xlFormulas, xlErrors)
    If Not rng Is Nothing Then
        .Replace "=Xabc", "abc", xlWhole, , False, , False, False
        rng.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(2)
        rng.EntireRow.Delete
    End If
End With

Ok, so I have diagnosed the issue. When trying to find that term in Column A - It is searching as One entire term. But, what I am searching is a part of the string.
I tried with xlPart, but it only renames the searched string and does not move it to the end of the page/bottom row.

" .Replace "=Xabc", "abc", xlPart, , False, , False, False"

Other strings/terms I was searching were (Whole) single string - so were working perfectly.

 

[RobP]

Hi,

in your code - where you have "=Xabc" - try using "=*Xabc*" instead, as this will find that piece of text then anywhere inside a bunch of other text in the cell.

Rgds
Rob

 

[TryingBest]

Hi,

in your code - where you have "=Xabc" - try using "=*Xabc*" instead, as this will find that piece of text then anywhere inside a bunch of other text in the cell.

Rgds
Rob

Sorry, still doesn't work. Is this how I should be running this? with xlpart ?

Sub MoveRows()

Columns("A").NumberFormat = "@"

Dim rng As Range
With Range("A:A")
.Replace "*Region*", "=R", xlPart, , False, , False, False
On Error Resume Next
Set rng = .SpecialCells(xlFormulas, xlErrors)
If Not rng Is Nothing Then
.Replace "=R", "Region", xlPart, , False, , False, False
rng.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(2)
rng.EntireRow.Delete
End If
End With


End Sub

 

[RobP]

My apologies - I've had to go back and re-read the thread from the top, as I'm getting confused by what you want to do. You talk about finding specific text, and moving those rows - but the code you post actually is replacing some text in certain cells. Are you just searching for text in order to move rows, or are you actually needing to replace some characters with other characters ?
thanks
Rob

 

[TryingBest]

My apologies - I've had to go back and re-read the thread from the top, as I'm getting confused by what you want to do. You talk about finding specific text, and moving those rows - but the code you post actually is replacing some text in certain cells. Are you just searching for text in order to move rows, or are you actually needing to replace some characters with other characters ?
thanks
Rob

Hi.. My requirement is still the same - I want to move a complete row based on a searched value (string/text) in a cell.
The code above works well with 'xlWhole' - and when the searched term is not a part of the string value.
So, to answer short - I am just searching for text in order to move rows - But text can be part of the string.