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ᴇʜ, Mar 01 '19 at 08:59

John Coleman · Accepted Answer · 2019-02-28T16:55:30.400

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 '19 at 16:55

answered Feb 28 '19 at 16:49

John Coleman

51,337
7
54
119

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? – Forward Ed Feb 28 '19 at 16:55
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). – John Coleman Feb 28 '19 at 16:56
%(&*@ 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. – Forward Ed Feb 28 '19 at 16:59
1

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](https://stackoverflow.com/q/4958189/4996248) for more. – John Coleman Feb 28 '19 at 17:04

rowindex using variables instead of hard coding

1 Answers1