2018-05-16 15:07:57 8 Comments

I have a simple macro that refuses to work. It gets to the cut portion and surrounds the information with marching ants; and then...nothing.

What I am trying to accomplish is to find a value (1050) in column D. Whenever I find it, I want to move the entire row of data to the bottom of the sheet.

The worksheet consists of thousands of lines of information and the number of rows changes every month. The columns are constant.

This is what I have:

```
Sub MurrayTest()
'Dim xRg As Range
'Dim xTxt As String
'Dim xCell As Range
'Dim xEndRow As Long
'Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Range("d:d")
xEndRow = xRg.Rows.Count + xRg.Row
Application.ScreenUpdating = False
For I = xRg.Rows.Count To 1 Step -1
If xRg.Cells(I) = "1050" Then
xRg.Cells(I).EntireRow.Cut
Rows(xEndRow).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub
```

Any assistance would be greatly appreciated!

### Related Questions

#### Sponsored Content

#### 1 Answered Questions

#### 3 Answered Questions

#### 1 Answered Questions

### [SOLVED] Combining IF else with LEFT to hide columns

**2009-10-28 11:11:19****Dan Newton****1543**View**2**Score**1**Answer- Tags: excel-vba show-hide if-statement vba excel

#### 1 Answered Questions

### [SOLVED] Excel VBA code for simple formula between cells

**2017-07-26 10:07:27****Rafael Osipov****304**View**0**Score**1**Answer- Tags: excel vba excel-formula range

## 2 comments

## @Ricardo A 2018-05-16 15:43:38

If i read this correctly, you want to find the value 1050 on Column D, if it is found you want this row Cut and Inserted at the end of the list.

## @n8. 2018-05-16 15:48:23

Hey great answer that is exactly the same as mine

thumbs up bro## @Ricardo A 2018-05-16 15:55:58

No problem. He is still using the last row of the sheet in the for loop by just applying your fix.

## @n8. 2018-05-16 15:30:08

Your problem is that you are trying to insert the rows into the last row of the sheet +1. This is out of bounds.

Instead of this:

Do this:

This way your just looking at populated cells instead of all possible cells.

NoteI prefer using something like`xEndRow = Cells(Rows.Count, 1).End(xlUp).Row`

because finding the last row from the top can sometimes be incorrect if the dataset isn't complete. You may need to play with different approaches to determine what's best for your situation.## @FloridaRick 2018-05-16 15:48:41

Thank you! That worked perfectly!