I'm an Excel noob, can I make it do the following somehow....

R

rocket1406

New Member
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!
 
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.
 
Last edited:
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 :))
 
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.
 
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...
 
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
 
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?
 
You must log in or register to reply here.
Back
Top