Find last row and delete from +1 to end

[Darin Kramer]

Hi all,

Im sure ive done this before, but cant get it to work now!

I want to using column A as the data source find the last piece of data,
and then delete from the next row (Ie keeping the last piece of data) to
the last row(65536)

eg if last piece of data is in A156, Macro needs to delete from
157:65536


Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

Dim NextRow as long
with activesheet
Nextrow = .cells(.rows.count,"A").end(xlup).row + 1
.rows(nextrow & ":" & .rows.count).delete
end with

 

[Darin Kramer]

Dave, I must be doing something wrong?? , it doesnt seem to delete the
rows? (I can tell cause they got cell borders all the way around them!)

*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

If you formatted the entire column with the borders, then excel knows to keep
that formatting.

If you select the last cell in column A and hit End followed by up arrow, do you
go to the last cell used in column A?

If you want to clear formats, you could use:

Dim NextRow As Long
With ActiveSheet
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
With .Rows(NextRow & ":" & .Rows.Count)
.Clear
.Delete
End With
End With