stephparker

I need a macro that searches Column L in the worksheet "CurrentOnlineStatements" for the word remove,
then cuts that row and pastes it in the next worksheet called "DeletedOnlineStatements"

But instead of pasting it in row 100, I need it to look for the next available empty row.

I also need it continue to search and not stop at the first row found.

Dim sc As Range
Dim foundrow As Long

For Each sc In Sheets("CurrentOnlineStatements").Range("L:L")
If sc.Value = "Remove" Then
foundrow = sc.Row
Exit For
End If
Next sc

If foundrow > 0 Then
Sheets("CurrentOnlineStatements").Rows(foundrow).Cut Sheets("DeletedOnlineStatements").Rows(100)
Sheets("CurrentOnlineStatements").Rows(foundrow).Delete xlUp
End If

Any Help would be great. Thanks sparker

David M58

Here's one way to do it.

Modify as necessary.

stephparker

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! It works perfectly! It will save us tons of time.

stephparker

I want to use the macro you gave me for another spreadsheet. I have made the adjustments for the different criteria. But I want to ask it to identify criteria 12 diffrent times and put each a different tab. I tried to use this macro more than once by renaming it. But it doesn't work if I rename the macro. Any suggestions?

Sub remove()
Dim sr As Long
Dim tr As Long
Dim cp() As Byte
ReDim cp(Sheets("CD").Range("B:B").Rows.Count) As Byte

For sr = 1 To Sheets("CD").Range("B:B").Rows.Count
If LCase(Sheets("CD").Cells(sr, 2).Value) = "Glenstone" Then
For tr = 1 To Sheets("Glenstone").Range("B:B").Rows.Count
If WorksheetFunction.CountA(Sheets("Glenstone").Rows(tr).EntireRow) = 0 Then
Sheets("CD").Rows(sr).Copy Sheets("Glenstone").Rows(tr)
cp(sr) = 1
Exit For
End If
Next tr
End If
Next sr

For sr = Sheets("CD").Range("B:B").Rows.Count To 1 Step -1
If cp(sr) = 1 Then
Sheets("CD").Rows(sr).EntireRow.delete
End If
Next sr
End Sub

David M58

There is a problem with one of the lines of code you posted:

If LCase(Sheets("CD").Cells(sr, 2).Value) = "Glenstone" Then

The problem is that the word "Glenstone" has a capital letter, but it is being compared to lowercase text with the LCase function, causing it to fail.

You can modify the line to read:

If LCase(Sheets("CD").Cells(sr, 2).Value) = "glenstone" Then

for a search that is not case-sensitive, or:

If Sheets("CD").Cells(sr, 2).Value = "Glenstone" Then

if you want a case-sensitive search.

stephparker

Thank you. I have it working for all my criteria with a few bugs I am still working out. You have been a great help!!! Thank sparker