[ExcelVBA] Re: Delete rows relative to Range.End(xlDown).Row

 

Thanks to Ralph for his interest and question. The code below is exactly what I was looking for since I am not looping through rows.
Nice catch!

--- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>
> 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
> 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@...>
> 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@...>
>
> 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 (3)

Recent Activity:

• New Members 2

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___