Excel 2000 - expanding and shrinking data 1

[srogers]

I am writing a macro to replace old data with new data (from a .txt file).

I gave the data a named range.
It works but when the new data has fewer rows than the old data some of the old rows are left. Example pasted below.
Any ideas how I can only show the new data in my worksheet?
Thanks,
Sharon

(new data ends where the square is and the row after
that is part of the old data)
561290127 1030803 202 93 14
457069718 1030803 204 93 14
288582637 1030803 205 45 42
458889972 1030803 205 65 14
467259812 1030803 205 95 28
458928505 1030803 206 93 14
643628937 1030803 207 51 13.3
450904458 1030803 207 61 8.75

450904458 1030803 207 61 8.75


 

[TonyJollans]

Hi srogers,

Here's one way which clears from the first column in the row below your range to the end of the (used part of the) sheet ..

Dim NamedRange As Range
Set NamedRange = Range("YourRangeName")
Cells(NamedRange(NamedRange.Rows.Count + 1, 1), _
      UsedRange(UsedRange.Cells.Count)).ClearContents

Enjoy,
Tony

 

[srogers]

I am sooo sorry it took me so long to reply.
I found a really cheesy way around this while I put out some other fires (I replaced a large block of cells with zeros!!)

I tried the code you offered and changed the "YourRangeName" to the name I gave the range being copied to.

The error I got said Object required and pointed at this line:
Cells(NamedRange(NamedRange.Rows.Count + 1, 1), _
UsedRange(UsedRange.Cells.Count)).ClearContents

I'm not sure what Object it is looking for unless it needs a reference to the worksheet?

Thank you. I appreciate your time.

 

[SkipVought]

Hi,

Check out my FAQ

faq68-1331

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

good grief - there is no need for code. Just use a "Dynamic Named Range"

Lets say your worksheet is called "Data" and your data starts in A1

Goto Insert>Name>Define
Enter a name
Instead of just entering a cell ref for the name, enter this:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

This will expand or shrink the range depending on how much data is there

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[TonyJollans]

Hi Geoff,

I really must learn to harness the power of Excel. Trouble is, I only think in code

Enjoy,
Tony

 

[xlbo]

Nothing wrong with a good bit 'o code but I reckon it should always be used if the native functionality is not there or if the functionality requires manual attention all the time (like standard named ranges)



Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[SkipVought]

Geoff,

What are you talking about --

"...requires manual attention all the time (like standard named ranges)"?

I used Named Ranges all the time, almost always names from the Top Row and often a table name based on the sheet name.

It's ALL done WITHOUT manual intervention as table expand and contract! Big advantage that the Ranges in Code and on Sheet are identical.

Am I missing something?

BTW, I did like the OFFSET code that you posted, and I would use it in cases where there is no code being used - like for a Pivot table.

So I give you my kudos!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

This is like code to me - where do you learn this stuff?
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Anyway, thank you.

I have questions though.
Column A is highlighted and then I get an error that says the copy and paste areas are not the same size and shape.

So I suppose I need to change the range to be copied to use the "Offset" also insead of the cell references.

Are the two zeros the height and width? (I read about Offset in "Help" but they speak a little above my head (like this for example: Reference is the reference from which you want to base the offset.)

And CountA looks at all the cells that are not empty right? But I only want a certain block of cells from the worksheetA (the worksheet I'm copying) to Worksheet "Data".
It doesn't seem intuitive to use an absolute cell reference when I want it to expand and contract but that just shows you what a newbie I am.
Thanks for putting up with all my questions and if you know of a good reference book - please let me know.

 

[srogers]

Thanks Skip for the FAQ reference.
I'm struggling to understand what it is doing.
I read in Help about tables to find out what it was (I know what a table in Access is). Is it just data on a worksheet represented by a named range?
Does it create a new range name each time it is run?
And the part that says Cells(1,1) is that referring to cell A1?

Thank you -

 

[SkipVought]

Geoff,

It seems to define the range, but it does not work as a reference in the Pivot Table Wizard...

1. I did Insert/Name/Define
dbData as
=OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1))

2. I substitute dbData in the Pivot Table Wizard "Where is the data that you wnat to use" window - Step 2 of 3

BUT...

it says that the PivotTable is invalid when I use this reference. In the process, the correct data range is outlined with dancing dashes, so it seems to select the correct range.

What's heppnin', yo great one?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Skip - I think you need to include a sheet name but dunno why it's having a fit - I've got loads of pivot tables based on dynamic ranges. Heck - half of my reports wouldn't work if that concept didn't work. BTW by manual intervention I mean that if you don't write some code to do it, you have to re-reference the named ranges when they change. With this method you don't - it does it for you so if you wanted to set up a chart or a pivot table on expanding / contracting data, you can do so without any code whatsoever

srogers - my suggestion was to use the dynamic named range so that you never have to re-reference it. This would allow you to use myRange.clearcontents
to clear the whole range. Then, when you paste the new data in, the named range will automatically expand to the size of the new data

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244