Actually, you are right to be concerned.
and in this case, I MAY have overlooked something.
He ACTUALLY said that he wanted to delete the two rows AFTER the "last row"
and the (1) row BEFORE the "last row".
But NOT necessarily the "last row"!
Therefore, he should use:
Range("A" & LastRow + 1 & ":A" & LastRow + 2).EntireRow.Delete
Range("A" & LastRow - 1).EntireRow.Delete
(in that order)
if he were wanting to delete the (4) rows from the lastrow -1 THROUGH
lastrow +2,
then the previous code would work.
The row count problem doesn't come into play because
Range("A" & LastRow - 1 & ":A" & LastRow + 2).EntireRow.Select
selects the (4) rows as a single selection and deletes them.
If we were looping through the rows, then you are correct,
we should loop from the bottom up
so that the row numbers don't change
nice catch.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Ralph Gregory ralph.gregory@skaino.co.uk>
To: ExcelVBA@yahoogroups.com
Sent: Thu, January 3, 2013 1:42:17 PM
Subject: Re: [ExcelVBA] Delete rows relative to Range.End(xlDown).Row
Just as a matter of interest would the lastrow not move up when its deleted?
Ie if youre last row is 5 and you delete the row before it then the two rows
after it that the op wanted deleting would now actualy be 5 & 6 of does the
count happen after
Ralph Gregory
Contracts Manager
Skaino Services
West March
DAVENTRY
NN11 4SA
----- Reply message -----
From: "Paul Schreiner" schreiner_paul@att.net>
To: ExcelVBA@yahoogroups.com>
Subject: [ExcelVBA] Delete rows relative to Range.End(xlDown).Row
Date: Thu, Jan 3, 2013 6:11 pm
Basically, you have to define the range of cells from LastRow -1 to Lastrow + 2
Range("A" & LastRow - 1 & ":A" & LastRow + 2).
Then, reference the entire rows by using ".EntireRow"
then tell it what you want to do with it (.Delete)
and you end up with:
Range("A" & LastRow - 1 & ":A" & LastRow + 2).EntireRow.Delete
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Manny mgarza@fellowes.com>
To: ExcelVBA@yahoogroups.com
Sent: Thu, January 3, 2013 12:36:55 PM
Subject: [ExcelVBA] Delete rows relative to Range.End(xlDown).Row
I want to delete rows in my spreadsheet relative to the last row returned using
this statement: LastRow = Worksheets("Sheet1").Range("A2").End(xlDown).Row
Specifically there will be 2 rows after LastRow that I want to delete and 1 row
before LastRow that I want to delete. What code would do this?
I hope I've given enough info but if not ask and I will fill in details.
Thanks! You guys are awesome.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar