Help in Writing VBA Macro excel

[Closed]
Report
Posts
1
Registration date
Thursday October 25, 2012
Status
Member
Last seen
October 25, 2012
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
Hello,

I wish to write a macro for following:

1. Delete entire rows if column L or column M contain DATE.
2. Move deleted entire rows to other sheet in same workbook.

Thanks.

1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Learningmacro,

I assumed the following:
Columns L and M has the same amount of data.
Column A always contains data.

Here you go:
Sub MoveAndDelete()
Dim lRow, faRow, x As Integer

lRow = Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet1").Range("L1:M" & lRow)
    If cell.Value = "DATE" Then
        faRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        cell.EntireRow.Copy Sheets("Sheet2").Range("A" & faRow)
        cell.EntireRow.ClearContents
    End If
Next cell

For x = lRow To 1 Step -1
    If Range("A" & x) = vbNullString Then Range("A" & x).EntireRow.Delete
Next x

End Sub

Best regards,
Trowa