rowindex using variables instead of hard coding

Question

I am writing a bit of VBA to delete a contiguous range of rows within a table. I found several example on how delete rows and most were with for selected row, or a selection of none contiguous rows. In this question it was set to loop through and delete one row at a time which would work. In another example, they had the following line which did it all in one shot

rows("4:8").delete

Now this seemed ideal to me. I have my start row set as a variable, and I have my last row set as a variable.

DIM First_Row as integer
DIM Last_Row as integer
    First_Row = 14
    Last_Row = First_Row + Application.worksheetfunction.MAx(Range("B:B")) -1

    Rows(First_Row:Last_Row).delete

'That last line is not working


    Rows("First_Row:Last_Row").delete

'nor the above

what is the proper syntax to delete a range of rows in one shot without a loop?

Note that row counting variables must be of type Long bacause Excel has more rows than Integer can handle. — Pᴇʜ
– Pᴇʜ, Commented Mar 1, 2019 at 8:59

John Coleman · Accepted Answer · 2019-02-28 16:55:30Z

1

Can be done in different ways, for example:

Range(Cells(First_Row,1),Cells(Last_Row,1)).EntireRow.Delete

Another, perhaps simpler way, is

Range(First_Row & ":" & Last_Row).Delete

You might want to use Long rather than Integer for your row number variables, since Integer can overflow with modern Excel.

edited Feb 28, 2019 at 16:55

answered Feb 28, 2019 at 16:49

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

4 Comments

Forward Ed Over a year ago

just decoding what you are saying about long versus integer...integer can have a max value of 32767 and the maximum number of rows in current excel versions exceeds that?

John Coleman Over a year ago

Yes: As an experiment, ?rows.Count evaluates to 1048576 in the immediate window (in Excel 2016, but it has been like that for a while now).

Forward Ed Over a year ago

%(&*@&# FIDDLE STICKS! If you do Range(First_Row&":"&Last_Row).Delete you get an error, but if you leave the spaces in Range(First_Row & ":" & Last_Row).Delete no error. sometimes syntax can be so frustrating.

John Coleman Over a year ago

The problem is due to the fact that some old-style Basics used type-suffixes where e.g. name$ was used for a string variable, and that is still there in a dusty corner of the syntax for backwards compatibility. See this question for more.

Collectives™ on Stack Overflow

rowindex using variables instead of hard coding

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related