VBA find and Cut

[Siyanna]

Hi All

what is the best way to find a specific value called Leaver in say range (A2:A50) and cut the entire row when found and paste it to the bottom deleting and blank cells in between?

my aim is to ensure that i have all leavers at the bottom of the data

 

[Rick Rothstein]

what is the best way to find a specific value called Leaver in say range (A2:A50)...

What exactly do you mean by "called"? Is Leaver the text in the cell, a Defined Name for a cell, something else?

 

[Siyanna]

Sorry Leaver is the text in the cell im looking for

 

[Rick Rothstein]

I don't not know if this is the best way (probably not), but here is one way to do what you asked for...

[table="width: 500"]
[tr]
	[td]Sub MoveLeaverToBottom()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Range("A2:A" & LastRow)
    .Replace "Leaver", "=Leaver", xlWhole, , False, , False, False
    On Error GoTo NoLeaver
    Intersect(.SpecialCells(xlFormulas, xlErrors).EntireRow, Columns("A").Resize(, 3)).Copy Cells(LastRow + 1, "A")
    .SpecialCells(xlFormulas).EntireRow.Delete
  End With
  Columns("A").SpecialCells(xlConstants, xlErrors).Value = "Leaver"
NoLeaver:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Siyanna]

thank you

 

[Siyanna]

I don't not know if this is the best way (probably not), but here is one way to do what you asked for...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MoveLeaverToBottom()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Range("A2:A" & LastRow)
    .Replace "Leaver", "=Leaver", xlWhole, , False, , False, False
    On Error GoTo NoLeaver
    Intersect(.SpecialCells(xlFormulas, xlErrors).EntireRow, Columns("A").Resize(, 3)).Copy Cells(LastRow + 1, "A")
    .SpecialCells(xlFormulas).EntireRow.Delete
  End With
  Columns("A").SpecialCells(xlConstants, xlErrors).Value = "Leaver"
NoLeaver:
  Application.ScreenUpdating = True
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick

Ive amended the code slightly so it copies to the last column

Only issue I have is that it shows an error(NAME)ERROR Message where it had the word Leaver where I still need to have that word there

here is the amendedcode

 

[Siyanna]

for some reason its not allowing me to copy and paste the code here but all I did was that added a last column variable and in the resize bit change the 3 to lastcol

 

[Rick Rothstein]

Only issue I have is that it shows an error(NAME)ERROR Message where it had the word Leaver where I still need to have that word there

Try changing this line of code...

Columns("A").SpecialCells(xlConstants, xlErrors).Value = "Leaver"

to this...

Columns("A").SpecialCells(xlFormulas, xlErrors).Value = "Leaver"