Help with Non Contiguous Row Ranges

[Chriz]

Hi All,

I've got the following code in use:

BookLastrow = Range("A3").End(xlDown).Row
DVDLastrow = Range("A739").End(xlDown).Row

'More Code yadayadayada

Set ALLISBN = Range("A3:A & BookLastrow, A739:A & DVDLastrow")

However I am getting a run time error 1004. Basically to cut a long story short I'm trying to future proof the macro so when people insert rows into the workbook the macro will handle it without me hard coding the different absolute references.

The absolute cell references as they are at the current time are:

Set ALL ISBN Range("A3:A736, A739:A1023")

I can get

Set ALLISBN = Range("A3:A & BookLastrow, A739:A & DVDLastrow")

to work only if I add speech marks to for example:

("A3:A" & Booklastrow, "A739:A" & DVDLastrow)

However the above also gives the cells inbetween e.g. A736 to A739 when I run the code through (which is not what I want). It's hopefully just a syntax thing if anyone can help?

Thanks

Chriz

[AndyLitch]

BookLastRow is going to return the same as DVDLastrow... i.e. the last row of data in column A... You'll have to find another method to determine the last row of book data

[gjcase]

I have addressed similar issues by using named ranges. For instance, if you name A3:A736 as RANGE1, then any rows inserted will be part of the range. So if someone adds 3 rows at row 4, RANGE1 will now refer to A3:A739. Just reference the range name in your macro instread of the absolute address.

Note that is someone tries to append a row (say row 737) to the range, this will not be part of the named range, because row 737 is not part of RANGE1.

Hope that helps.

[Chriz]

Ok many thanks both for your replies. I'm going to give the Named ranges a go, sounds like an ideal solution and will mark this as solved.