Help in Writing VBA Macro excel

Closed
learningmacro
Posts
1
Registration date
Thursday October 25, 2012
Status
Member
Last seen
October 25, 2012
- Oct 25, 2012 at 10:37 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Oct 30, 2012 at 10:56 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
512
Oct 30, 2012 at 10:56 AM
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
0