Copying and shifting filtered data by one row & maintaining the da

[Copy filtered column data & shift row up]

I have a column of numbers which contain blank rows in between the numbers.
The blanks are in no partcular interval meaning there can be 2 blanks, 3
blanks or 10 blanks or greater between numbers.
What I am trying to do is to filter the data by numbers only (done) & copy
these numbers & paste it into another column within the same sheet by
shifting the entire data by one row up.
The pasted columns should look exactly like the filtered column except that
the pasted column will start one row up & there will be a blank on the last
row. is this possible within the tools available in excel or do I need to
write VBA code for it?

 

[Conan Kelly]

Copy filtered column data & shift row up,

This should be possible with out VBA, but it all depends on what you want
for results.

1. Do you want these numbers to be in consecutive cells? Keep in mind that
you are working with a worksheet that is already filtered, so if you want
all of these numbers in consecutive cells, some of the cells might be hidden
because your filter turned on and hiding rows. If this is what you want,
then select all of the numbers, then select just the visible cells (Edit
menu > Go To... ([Ctrl] + G)) > Special... button > Visible cells only radio
button) (or [Alt] + [;]) (those instructions are for XL 2002/2003...don't
know XL 2007), move to the cell in the column you want one row up from the
top number, paste (or paste special...depending on your needs).

2. If you want to copy all the numbers with the exact same number of blank
cells in between each number, then I would just copy the column to the
desired location and the delete/remove a cell from the column above the
first number, shifting all of the cells up. Once again, many (if not all)
of the numbers will be hidden because of the filtering that is turned on.

3. Now if you want to copy all of these numbers into another column
offsetting by one row OF THE VISIBLE, FILTERED ROWS, then that is a little
more complicated and would most likely need VBA.

If what you need is the last option, then write back with sample data (be
sure to include blank cells between numbers) and someone here might come up
with some VBA code that will do what you want.

If what you want is option number 1 or 2 and you have to do this very often
in many different files, then you could create VBA code that will automate
this for you.

Also, right back if you have any more questions.

HTH,

Conan





"Copy filtered column data & shift row up" <Copy filtered column data &
shift row (e-mail address removed)> wrote in message
news:[email protected]...

 

[Gord Dibben]

If you want to remove the blank rows without filtering.

F5>Special>Blanks>OK

Edit>Delete>Entire Row


Gord Dibben MS Excel MVP