rocket1406

Hey guys.

So basically in my spreadsheet I have a bunch of blue "blocks" of numbers in between a black sheet of numbers.

(basically my important data is in blue)

My teacher wants me to delete all the black text on the spreadsheet except for one black field above and below every blue block.

It looks like this

Black
Black
"Black"
Blue
Blue
"Black"
Black
Black

I have to delete the Black, but keep the "Black" and the Blues.

So I have to do Right Click --> Delete --> Shift Cells Up --> OK, like 100 times.

Is there anyway I could make Excel do that, but the command be:
Right Click --> Tab button

and have it do the same thing?

Thanks a lot, some shortcut like that would save me HOURS.

Please help. Thanks in advance!

Platinum

If you drag a black cell over another black cell and allow it to clear the contents of the cell, you end up with only 1 black cell.

So if you take the first black cell, and drag it over the next black cell, let it clear the cell, the first black cell turns white. Using Ctrl to select multiple cells will make that process faster.

__________________
A computer once beat me at chess, but it was no match for me at kick boxing.

(______(________________((()
SIG-ARETTE

apj101

you could create a new macro with the following code
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function


then just goto the row to the right of all the data, start at the top do a =if(cellcolorindex(A1,TRUE),A1,"")
fill that down, and manually enter the word black above your blue blocks (you could prob strucutre the forumla to do that for you are well, if you can be bothered to nest a few more if statements )

__________________
What did one snow man say to the other?
can you smell carrot?

The fight is won or lost far away from witnesses - behind the lines, in the gym, and out there on the road, long before I dance under those lights.

How you do anything, is how you do everything!

rocket1406

how do I make that macro?

And if I copy paste exactly what you said it will work?

(I don't know how to program)

rocket1406

http://www.putfile.com/pic.php?img=5810668

rocket1406

so

red
black - del
black - del
black
blue

and if it's

blue
black
black - del
black
blue

basically delete all black that is NOT touching a blue. if it's touching a blue, I can't delete it because I need those values.

rocket1406

They can shift up as long as only the correct pair is shifting. For example, Column A/B is independent of Column D/E.

If Column A/B shifts up, Column D/E should NOT.

I've been doing Right Click --> Delete --> Shift Cells Up

Eventually all that should be left is red, a little bit of black, and mostly blues.

So it will end up something like:

Red
Black
Blue
Blue
Blue
Blue
Black
Black
Blue
Blue
Blue
Blue
Blue
Black
Black
Blue
Blue
Blue
Blue
Black
Red
Black
Blue
Blue
Black
Black
Blue
Blue

etc...

apj101

without macros you will have to do this manually. And it would be quicker to do manually than it would to teach you vb...sorry mate

__________________
What did one snow man say to the other?
can you smell carrot?

The fight is won or lost far away from witnesses - behind the lines, in the gym, and out there on the road, long before I dance under those lights.

How you do anything, is how you do everything!

rocket1406

If it helps, I can change the colors of the black cells that I don't want to be deleted to another color, Purple.

Then can we do a command that can do:

Right click Delete --> Shift Cells Up --> Ok

for ANY and every black text cell?