VBA : Delete rows using variables

Question

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.

If the worksheet you're using Select on isn't the ActiveSheet, you'll get a run-time error 1004 for 2 reasons: the Cells calls aren't qualified, and you can't select cells on the non-active sheet. Qualify all your references, and use the Range objects - not the selection. — Comintern
– Comintern, Commented Mar 8, 2017 at 14:06

R3uK · Accepted Answer · 2017-03-08 14:04:52Z

3

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

answered Mar 8, 2017 at 14:02

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wouldn't .Range(.Rows(2), .Rows(z)) be a bit cleaner?

@Comintern : It sure would be I wanted to illustrate the use of EntireRow, I'll edit to include it ;)

@FrancisZHENG : Glad I could help! ;)

Ahmed AbdelKhalek · Accepted Answer · 2017-03-08 14:01:33Z

1

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

Rows("2:" & Z).Delete

answered Mar 8, 2017 at 14:01

Ahmed AbdelKhalek

1922 silver badges11 bronze badges

Thank you for the help !