Problem with changing defined ranges...

[BeSmart]

Hi all
I have a code using defined ranges....

The defined range on a worksheet called "Schedule" will increase/decrease in
size based on users inserting/deleting rows.

When the code copies/pastes values/formats data on a second worksheet
(called "Thousands") it uses defined ranges to format the data, but it's
missing rows because the defined areas of worksheet "Thousands" no longer
match those of "Schedule"....

How do I write the code to format data on the worksheet "Thousands" when the
number of rows increases/decreases each time?


Defined names and ranges used in code:
"FullThousands" =Thousands!$1:$65536
"Pasteschedule2" =Thousands!$A$13:$DZ$100
"Schedule" =Schedule!$A$13:$DZ$71
"Pasteposition" =Thousands!$A$13
"Thousandsspots" =Thousands!$AC$22:$DP$100
"Sortarea" =Thousands!$A$22:$DZ$100
"Subtotals" =Thousands!$A$21:$DZ$100
"Unusedtotals" =Thousands!$DS:$DX

What the code does:-

For Worksheet "Thousands"
(These steps clear prior formatting to start with a clean range)
- Select entire worksheet ("FullThousands") and clear current subtotalling

- Select all rows from 13 onwards (range defined as "Pasteschedule2") and
clear contents.
===================================
I think the above is causing my defined areas to change....?
===================================
For Worksheet "Schedule"
- Copies defined range named "Schedule".

For Worksheet "Thousands"
- Pastes that data (pasting formatting and values only) starting at a
defined range named "Pasteposition"

- Select all again ("FullThousands") and clear interior Colouring.

- Select defined range "Thousandsspots"
- paste a formula in each cell to convert spots to thousands
- format the numbers
- copy and paste values only to get rid of formulas.
- Find and replace any zero cells with "".

- Sorts all columns in defined range named "Sortarea" into ascending order
based on data in column "L"

- Subtotals all columns in defined range named "Subtotals" based on data in
column "L" and only show the totals
===========================
This looks bad because the Grand Total ends up many rows below the totals
===========================

Lastly there is some basic formatting...

Here is the current code:

Sub Thousands()
Dim txt1 As String
txt1 =
"=IF(AND(Schedule!$H22<0,Schedule!AC22<0),0,IF(AND(Schedule!$H22>0,Schedule!AC22>0),$Y22*IF($Z22>0,$Z22/1000,Schedule!$CF$6/1000),Schedule!AC22))"
Application.ScreenUpdating = False
Application.Goto Reference:="FullThousands"
Selection.RemoveSubtotal
Application.Goto Reference:="pastedschedule2"
Selection.Clear
Range("L22").Select
With ThisWorkbook.Names("Schedule").RefersToRange
.Copy
With ThisWorkbook.Names("Pasteposition").RefersToRange
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="FullThousands"
Application.CutCopyMode = False
Selection.Interior.ColorIndex = xlNone
With ThisWorkbook.Names("Thousandsspots").RefersToRange
.Formula = txt1
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
.NumberFormat = "#,##0.00"
With .Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Range("L22").Select
Application.Goto Reference:="Sortarea"
Selection.Sort Key1:=Range("L22"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DisplayAlerts = False
Application.Goto Reference:="Subtotalarea"
Selection.Subtotal GroupBy:=12, Function:=xlSum, TotalList:=Array(29,
30, _
31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
48, 49, 50, 51, 52, 53, 54, 55, 56, _
57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 80, 81, 82, _
83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99,
100, 101, 102, 103, 104, 105, 106, _
107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119,
121, 122, 129, 130), Replace _
:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Application.DisplayAlerts = True
Range("AB15").Select
ActiveCell.FormulaR1C1 = "(NOTE: All figures are in '000's)"
With ThisWorkbook.Names("Unusedtotals").RefersToRange
.ClearContents
End With
Application.ScreenUpdating = True
End With
End With
End Sub

 

[Arvi Laanemets]

Hi

When named range is used as external source in some workbook, the following
doesn't apply. Otherwise you can use dynamic named ranges, or you can define
the fixed named range using OFFSET function.

An example for dynamic named ranges (there must be at least one column
always filled, the 1st row in table must be filled, and no empty rows or
columns can be in table):
FullThousands=OFFSET(Thousands!$A$1,,,COUNTIF(Thousands!$A:$A,"<>")-1,COUNTI
F(Thousands!$1:$1)-1)

An example for fixed named range:
FullThousands=OFFSET(Thousands!$A$1,,,65535,33)


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

 

[BeSmart]

Thanks Arvi

Questions:

The worksheet will have some empty cells/rows/columns do it sounds like
dynamic ramed ranges are not an option.

With the fixed named range using the OFFSET function:

- if the action is to copy and paste values only on a range of
$A1,,,65535,33 will that range activate all cells down to 65535,33? If so
won't that will make my file size huge?
I've found in the paste that even if pasting blank cells excel activates the
cell and the file size increases dramatically.

- Second, your example of a fixed named range is to cover the entire
worksheet, what if I only want AC22O100? How is that written?

Lastly, (this might be a dumb question but I'm a novice so I'm checking
everything I do...)Is this how I define the ranges as part of the code:

Sub Example()
DIM FullThousands As String
DIM Pasteschedule2 As String
FullThousands=OFFSET(Thousands!$A$1,,,65535,33)
Pasteschedule2=OFFSET(Thousands!$AC$22,,,$DO$100)
.....

Thanks heaps for your help
BeSmar
---------------------------------------------------------------------------------------

Hi

When named range is used as external source in some workbook, the following
doesn't apply. Otherwise you can use dynamic named ranges, or you can define
the fixed named range using OFFSET function.

An example for dynamic named ranges (there must be at least one column
always filled, the 1st row in table must be filled, and no empty rows or
columns can be in table):
FullThousands=OFFSET(Thousands!$A$1,,,COUNTIF(Thousands!$A:$A,"<>")-1,COUNTI
F(Thousands!$1:$1)-1)

An example for fixed named range:
FullThousands=OFFSET(Thousands!$A$1,,,65535,33)


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

 

[Arvi Laanemets]

Hi

Thanks Arvi

Questions:

The worksheet will have some empty cells/rows/columns do it sounds like
dynamic ramed ranges are not an option.

Not exactly. There are some possibilities. P.e. you can have a (hidden)
column, where row number for every row until last filled cell(s) in
comparision column(s) is calaculated. Of-course it means you must have some
amount of empty rows prepared.
An example for case, where cells in column B for every filled row are
non-empty, and data start from row 2 (row1 is for headers)
A2=IF(AND(A3="",B2=""),"",ROW())
Copy the formula down for as much rows you think as reasonable.
Now you can define a dynamic range, based on column A.
When you have some fixed number of rows, you'll add every session, or when
there is some limit for number of added rows in a session, then you can
write an Open event, which estimates the number of predefined rows in table,
and when needed, copies the formula in last filled cell of column A down,
until number of predefined rows is what needed.

With the fixed named range using the OFFSET function:

- if the action is to copy and paste values only on a range of
$A1,,,65535,33 will that range activate all cells down to 65535,33? If so
won't that will make my file size huge?

You need those ranges for select the copy range? I haven't used them in such
way - I use dynamic named ranges in various worksheet formulas, and fixed
dynamic ranges as sources for ODBC queries. So I'm on wakling ground here -
maybe somebody other can help you.

I've found in the paste that even if pasting blank cells excel activates the
cell and the file size increases dramatically.

- Second, your example of a fixed named range is to cover the entire
worksheet, what if I only want AC22O100? How is that written?
=OFFSET(Thousands!$AC$22,,,79,91)



Lastly, (this might be a dumb question but I'm a novice so I'm checking
everything I do...)Is this how I define the ranges as part of the code:

Sub Example()
DIM FullThousands As String
DIM Pasteschedule2 As String
FullThousands=OFFSET(Thousands!$A$1,,,65535,33)
Pasteschedule2=OFFSET(Thousands!$AC$22,,,$DO$100)
.....

Haven't created them in VBA. But under Items I could read all parameters for
all defined named ranges in Watches window, when I typed in
Application.Names

To read parameters p.e. for a named range 'test', I'd type in
Application.Names("test")

So probably you have to add a Name object, and set values for it's
parameters in VBA, or to modify parameters for an existing Name object.

Sorry, but your code above is nonsens. You define a couple of string
variables, and then you attempt to set them to be range objects!!! The best
you can do with it will be:
FullThousands="=OFFSET(Thousands!$A$1,,,65535,33)"
Pasteschedule2="=OFFSET(Thousands!$AC$22,,,$DO$100)"
so you can use them later to determine RefersTo or Value parameters for
named ranges.


Arvi Laanemets

 

[Jamie Collins]

fixed dynamic ranges as sources for ODBC queries

Sounds interesting. Can you give an example?

Many thanks,

Jamie.

--

 

[Arvi Laanemets]

Of-course 'dynamic' was a typo! Thanks!

 

[Jamie Collins]

Of-course 'dynamic' was a typo! Thanks!

Oh :-(

Jamie.

--