Solved: remove/delete cells

[Pete]

Hi

Need a macro to find the last cells in columns I and J starting at row 6 for both columns with data in. then delete the next three rows.

[GTO]

Okay, I'll bite...

If they are the last cells, them what are we deleting? If its data in other columns, then shouldn't we be careful as if Col I or J is "shorter" which one do we base the saying adios to rows by?

Sorry if I am just not 'catching' what you are saying.

Mark

[Bob Phillips]

[vba]

With Activesheet

LastRow = .Cells(.Rows.Count, "I").End(xluP).Row
LastRowJ = .Cells(.Rows.Count, "J").End(xluP).Row
If LastRowJ > LastRow Then LastRow = LastRowJ
For i = LastRow to 6

If .Cells(i, "I").Value <> "" And .Cells(i, "J").Value <> "" Then

.Rows(i + 1).Resize(3).Delete
Exit For
End If
Next i
End With
[/vba]

[Bob Phillips]

Okay, I'll bite...

If they are the last cells, them what are we deleting? If its data in other columns, then shouldn't we be careful as if Col I or J is "shorter" which one do we base the saying adios to rows by?

Last row with data in BOTH!

Don't know why just 3 rows though, why not al?

[Pete]

hi xld

i amended changed For i = LastRow To 6 to For i = LastRow To 6 step -1

and the macro still does not delete.

Example if the last row in column I and J is 122228 then delete rows 122229 to 122231....

the last row in columns I and J will be different each month...also

[Bob Phillips]

I'm having a bad day. Try

[vba]

With ActiveSheet

LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
LastRowJ = .Cells(.Rows.Count, "J").End(xlUp).Row
If LastRowJ < LastRow Then LastRow = LastRowJ
i = LastRow
Do Until .Cells(i, "I").Value <> "" And .Cells(i, "J").Value <> "" Or i < 6

i = i - 1
Loop
If i >= 6 Then .Rows(i + 1).Resize(3).Delete
[/vba]

[GTO]

Last row with data in BOTH!

Ohhh... that''s what that thar word means

[Pete]

thanks sir