By FloridaRick


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!

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.

Sub MurrayTest()
    Dim xRg As Range
    Dim xEndRow As Long
    Dim i As Long

    Set xRg = Range("D:D")
    xEndRow = Cells(Rows.Count, 4).End(Excel.xlUp).Row + xRg.Row

    Application.ScreenUpdating = False
    For i = xEndRow 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

@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:

Set xRg = Range("d:d")
xEndRow = xRg.Rows.Count + xRg.Row

Do this:

Set xRg = Range("d:d")
xEndRow = Range("d1").End(xlDown).Row + 1

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

Note I 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!

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] How to lock a column until it's last row with data

  • 2016-09-23 11:14:34
  • Manpreet Gujral
  • 474 View
  • 1 Score
  • 1 Answer
  • Tags:   excel excel-vba vba

3 Answered Questions

[SOLVED] Excel VBA macro to send emails to unique users in range

  • 2018-07-02 17:22:03
  • HunterTTP
  • 235 View
  • 1 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Combining IF else with LEFT to hide columns

1 Answered Questions

[SOLVED] Excel VBA code for simple formula between cells

1 Answered Questions

[SOLVED] how to read same data from two sheet then subtract the data using vba

  • 2017-05-17 07:54:36
  • Mohammad Shaffiq Rahim
  • 420 View
  • 0 Score
  • 1 Answer
  • Tags:   vba excel-vba excel

1 Answered Questions

[SOLVED] Hide Selected Columns in Excel using (VBA) Click Button

0 Answered Questions

Show only selected table column after filter to new worksheets

  • 2017-02-24 08:36:04
  • ladydee94
  • 258 View
  • 2 Score
  • 0 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

copy data from only 3 sheets and paste it in new sheet - Excel

0 Answered Questions

last row value returns blank with only one data row

1 Answered Questions

[SOLVED] Copy certain excel columns based on ones criteria

Sponsored Content