Solved

Excel VBA Code to Delete Last Row

Posted on 2010-08-12
5
879 Views
Last Modified: 2012-05-10
Just a simple VBA question...

I need to find the last row in the active worksheet with data in any column and then delete that row.

What's the VBA script to do that?
0
Comment
Question by:glennes
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 63 total points
ID: 33423409
activesheet.Cells.End(xlDown).EntireRow.Delete
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 62 total points
ID: 33423410
activesheet.usedrange.rows(activesheet.usedrange.rows.count).entirerow.delete
0
 

Author Closing Comment

by:glennes
ID: 33423477
Thanks!
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33423497
kiwi, why are these not the same?

ActiveSheet.UsedRange.Cells.End(xlDown).Row
ActiveSheet.UsedRange.Rows.Count
I've never understood why?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33423759
If your sheet only has data in the range E5-J10,

ActiveSheet.UsedRange = E5-J10
ActiveSheet.UsedRange.Rows.Count = 6
ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count) << row 6 of the range, so E10-J10

ActiveSheet.UsedRange.Cells.End(xlDown).Row  '' don't know never used this, result is unpredictable

FWIW, the accepted answer is incorrect, try putting data in c2,c3,i7 and running it.

activesheet.Cells.End(xlDown).EntireRow.Select   << goes to last row in sheet, not data

I am not sure if the question should be reopened to rectify the error.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now