'How to copy and paste rows before deleting them in excel VBA
I am looking to filter out a set of data with the criteria being if column A has over 5 characters in the string delete it.
However, before I delete it, I want to copy these entries to a sheet named "fixed"
The code I have at the moment works for the first entry, but doesn't loop through and I am unsure how to fix that...
Code:
Dim LR As Long, i As Long
LR = Worksheets("Output Sheet").Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Len(Range("A" & i).Value) >= 5 Then
Rows(i).EntireRow.Cut Worksheets("Fixed").Range("A:D")
Rows(i).Delete
End If
Next i
The data it is copying has 4 columns if that's of any help? I just can't seem to figure out why it doens't look but I am nearly positive it's a simple fix so any pointers would be appreciated.
Dim f As Long
Set Rng = Worksheets("Black List").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Application.ScreenUpdating = False
With Worksheets("Output Sheet")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For f = Lastrow To 1 Step -1
If Not IsError(Application.Match(.Range("A" & f).Value, Rng, 0)) Then
.Rows(f).Delete
End If
Next f
End With
Application.ScreenUpdating = True
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|