Ranges

G

Guest

I realize Excel is not the best tool for this but this is where I need to
start. I have a multi-worksheet file. One worksheet has raw data, over 700
rows, 20 columns. The other worksheets have a variety of complex formulas,
conditionals, and arrays. Over time I will be adding more rows. Is the best
way to handle this without changing every formula to name each range and
reset the range when I add rows.

Thanks.
 
G

Guest

hi
the trick is to reset the range automaticly using an event like the before
save event.
this macro will reset the range each time you save the file. it will
remember where you are in the file and return to the place you were at after
the macro has run wheather you are adding rows or doing something else. you
may notice a quick "blip" as the macro runs. you will have to adjust the
macro to fit your data ie your sheet name and your range name.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim rng As Range
Dim sht As Worksheet
dim srng as range

Set rng = ActiveCell
Set sht = ActiveSheet

Sheets("yourSheetName").Select
Range("A1").Select
set srng =Range(Range("A1"), _
Range("A1").Offset(0, 1) _
.End(xlDown).Offset(0, 20))
ActiveWorkbook.Names.Add Name:="YourRangeName", _
RefersToR1C1:= srng

sht.Select
rng.Select

End Sub

if you have multiple sheet and multiple ranges then add to the above macro
to accomodate the additional sheets and ranges.

Regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top