Need Edit Help: Macro is leaving blank row behind after moving row to another tab.

ERICinWV

New Member
Joined
Jan 28, 2016
Messages
2
Hello all,
I found a macro to move a row to another tab in same workbook based upon whether there was a particular value.
I edited the sheet names and then cell reference range to a column in my workbook.
The macro did move the correct rows to the second tab, however, it left behind blank rows where it had cut the data from.
Note: This was not a very quick running macro - don't know if it is how written or just my computer speed. Sheet had less than 500 rows.
After running, I had to filter by blanks and then delete rows to get them out of Current tab.
When the macro is run, I would like the macro to automatically delete the row from "Current" tab after moving row to "Gone..." tab.
I would imagine I just need to add a line, but I am very new to learning macros.
Any help would be appreciated.
In advance, thank you for your time and help!

Here is my current macro:

Sub Get_Rid_of_X()
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
Application.ScreenUpdating = False
lastrow = Worksheets("CURRENT").UsedRange.Rows.Count
lastrow2 = Worksheets("Gone or Not Needed").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 0
For r = lastrow To 2 Step -1
If Range("B" & r).Value = "X" Then
Rows(r).Cut Destination:=Worksheets("Gone or Not Needed").Range("A" & lastrow2 + 1)
lastrow2 = lastrow2 + 1
Else:
End If
Next r
Application.ScreenUpdating = True
End Sub
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
Hi,

Does this do what you want...

Code:
Sub Get_Rid_of_X()
    Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
    Application.ScreenUpdating = False
    lastrow = Worksheets("CURRENT").UsedRange.Rows.Count
    lastrow2 = Worksheets("Gone or Not Needed").UsedRange.Rows.Count
    If lastrow2 = 1 Then lastrow2 = 0
    For r = lastrow To 2 Step -1
        If Range("B" & r).Value = "X" Then
            Rows(r).Cut Destination:=Worksheets("Gone or Not Needed").Range("A" & lastrow2 + 1)
            Rows(r).EntireRow.Delete
            lastrow2 = lastrow2 + 1
        Else:
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

igold
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
Great, glad I could help. Thanks for the feedback!

igold
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top