CODEWITHSUNDEEP
excelvba
Lupi
Lupi

Reputation: 33

VBA accounting for inserted rows

I am making a scheduling worksheet with various macros which apply to certain ranges. The sheet is currently setup to where it should have enough rows for most days, but occasionally might need more.

Currently when a row is added(or deleted), the macro isn't accounting for this and the range stays the same, if it is possible I would like for it to account for the added rows. Here's an example of one of my macros-

Sub Print1st()

'

' Print1st Macro

X = Application.Dialogs(xlDialogPrinterSetup).Show

If X = False Then Exit Sub 

Range("A1:N40").PrintOut
Range("A4").Select

End Sub

So for instance, if two rows are inserted it I would like it to reference A1:N42 so that when it prints it is still printing everything and not cutting off two rows. If not, I can just lock users out from inserting rows and adjust later if it isn't sufficient.

Thanks for any help!

Upvotes: 0

Views: 55

Answers (1)

Alex K.
Alex K.

Reputation: 175758

You can name the range in the GUI or in code:

Range("A1:N40").Name = "printRange"

User inserted (not pasted) rows within that range will extend the range so after adding a row Range("printRange") is now A1:N41

Upvotes: 1

Related Questions