View Full Version : Solved: remove/delete cells
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.
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
12-03-2009, 05:30 AM
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
Bob Phillips
12-03-2009, 05:31 AM
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?
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
12-03-2009, 06:48 AM
I'm having a bad day. Try
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
Last row with data in BOTH!
Ohhh... that''s what that thar word means :doh:
:deepsleep
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.