Shift range down by x amount of rows

**davey4444** · 11-25-2010, 07:11 AM

Hi there.

I currently have 2 sheets in my workbook, the first contains data which is populated by a SQL source which is manually refreshed. Once the data is refreshed it gets pasted to the other sheet where users then write comments next to the entries.
The source data is sorted by week and the most recent is at the top.

I use an advanced filter (Excel 2007) to show just data relating to the current week which is working OK so far. The problem arises where I want to then shift all of the comments down so that they match the correct rows.

In my example the data in "working sheet e5:f7" was originally listed against data from week 46 until I updated it.
Could I apply some sort of count to see how many new rows have been added (filtered) and then shift the info in e6:f7 down by that amount?

Thanks in advance.

**TMS** · 11-25-2010, 07:21 AM

One way, maybe ...

If I want to keep track of the order that my initial/raw data is input, I quite often add a column with a "row counter". This can either have a formula, =ROW(), which I copy and PasteSpecial values when the input stage is complete, or simply AutoFill down a range starting with the first row of data.

That would give you a link between where the data started out and where it is after any additions.

Good film, BTW ;-)

Regards

**davey4444** · 11-25-2010, 07:30 AM

Thanks TMShucks. I think that I'm have a mental block or I wasn't at all clear in my description (or a mixture of them both) as I'd like to shift the 'manual' data automatically though VBA and can't quite fathom how the count works.

TIA

**Maistrye** · 11-25-2010, 12:58 PM

Hi,

You're using an advanced filter, so it doesn't really matter if the data goes on the top or the bottom, does it? You could potentially just copy the current week's data from the Source Data and put it on the bottom of your data in the Working sheet. (Making a slight change to the formula in C2 -- perhaps to one listed below)

Alternatively, you could change the sort order on the SQL query so it produces the data in week ascending order and copy it that way.

In terms of adding the proper number of rows at the top manually, you may want to consider the following formulas on the Working sheet:

PHP Code:


C2: =MAX('Source Data'!C:C)  .... or ='Source Data'!C2

D2: =COUNTIF('Source Data'!C:C,C2)

In this case, D2 would contain the number of entries for the current week.

If you're wanting to use VBA, basically you could just count the number of times the current week (in 'Source Data'!C2) appears in the source. Then you'd just have the macro insert that many lines at the top of your Working sheet and paste in the new data.

S