delete the row above

[oddworld]

hi all I have the following to delete the last non-empty row

[Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete]

I also need to add to the above code once it finds this row to delete the row immediately above it. Can someone please assist.

 

[pmich]

If you want to delete the last row and the row above, try this:

Sheets("sheet1").Cells(Rows.Count - 1, 1).End(xlUp).EntireRow.Delete
Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete

First you must delete the row above the last row.

 

[MARK858]

Untested because I am on my phone but maybe...

Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(-1).Resize(2).EntireRow.Delete

 

[pmich]

Mr. MARK858
I have never used Resize. Your one line code has replaced my two line code. Thanks.
Your code works fine.
I am still unable to understand what your code does.
End(xlUp) goes to last row, I guess. Then Offset(-1) moves to the row above.
Does Resize selects the last row and the row above?
When and where should I use Resize? I am unable to find an answer on the web. Please reply, if it is okay in this forum.

 

[MARK858]

See the links below...

Range.Resize Property (Excel)

Excel VBA Programming - The Resize Property

 

[My Aswer Is This]

Yes pmich Marks script deletes both rows at once. No need to use "select"

Here is a couple examples of how Resize works. There are many more.
Run this script and see what it does.
Resize(10) means 10 cells down in this example
It will fill the first 10 cells in column "A" with "Me"

The "10" means 10 cells Down

In second example you see Resize(10,5) which means 10 cells down and 5 columns to the right.
This script will fill those cells with "You"

Sub Resize_Me()
Cells(1, 1).Resize(10).Value = "Me"
Cells(1, 2).Resize(10, 5).Value = "You"
End Sub

 

[pmich]

Thanks, Mr. Mark.
The second one is very good. I need time to practice each code.
Thanks for suggesting.

Thanks, MyAnswerIsThis!
I just tried your code and it was a pleasant surprise. Having spent a few years with Excel VBA, Resize is news to me. The other day another expert told me about Enum.

I thank all the experts in this forum. Great Work indeed.