CODEWITHSUNDEEP
vbaexcelruntime-errorexcel-2007
Francis
Francis

Reputation: 121

VBA : Delete rows using variables

I wanted to delete rows from 2 to z (z > 2). I wrote 2 methods but neither worked. I searched for answers but didn't find a solution.

'.Rows("2:" & z).Select  
.Range(Cells(2, 1), Cells(z, 10)).Select  
Selection.Delete Shift:=xlUp

Thank you in advance.

Upvotes: 2

Views: 14308

Answers (2)

R3uK
R3uK

Reputation: 14547

You need to specify you want to select the rows with EntireRow, as your range is only a few cells :

.Range(.Cells(2, 1), .Cells(z, 10)).EntireRow.Delete Shift:=xlUp

Or cleaner ways if you directly use Rows :

.Range(.Rows(2), .Rows(z)).Delete Shift:=xlUp

Or

.Rows("2:" & z).Delete Shift:=xlUp

Upvotes: 3

Ahmed AbdelKhalek
Ahmed AbdelKhalek

Reputation: 192

You can use this simple one line to delete any number of rows you need.

Rows("2:" & Z).Delete

Upvotes: 1

Related Questions