deleting every other cell in a column

[bullseye]

I googled this question but am unable to understand how to use the programs
(or scripts) offered as a solution.
I am looking for a simple solution as I cannot program or use macro's. I
want to delete every other cell in a column where the values in these cells
happen to be the same three letters: QCO. (I found a solution for deleting
every other cell where those cells had no value:
Edit/Goto/Special/Blanks/Delete.)

Is there a similar set of commands I could use in Excel to delete every
other cell in a column where the value in those cells remains constant?

thanking all in advance

 

[Frank Kabel]

Hi
try applying a filter first ('Data - Filter') and delete the filtered
rows/cells.

 

[bullseye]

It is a bit cumbersome but it works. I isolated the cells using
Data-Filter, deleted them, and the worksheet ends up with alternating blank
cells when I "show all" to restore the values I want. Then
Edit/Goto/Special/Blanks/Delete gives me what I want.

If you could suggest a way to cut out a step or two from what I am doing
above, I would appreciate it even more.

thank you

 

[Frank Kabel]

Hi
not without using VBA

 

[bullseye]

Thank you.

Hi
not without using VBA

 

[Dave Peterson]

You sure you just didn't clearcontents. You may want to try it again to see if
you actually deleted (Edit|delete|entirerow) the rows.

 

[Gord Dibben]

If you are deleting entire row, you should not be left with any blank rows.

Are you deleting the rows or clearing the contents?

Gord Dibben Excel MVP

 

[bullseye]

I am only deleting alternating cells in a column; there is no row (but 400
to 500 cells in the column). Using the Data-Filter steps, it appears to me
that I am deleting the contents only because I am forced to go to
Edit/Goto/Special/Blanks/Delete and "shift cells up" to delete the empty
cells.
If there is a way to delete the cells along with their contents using either
Data-Filter or any other method, I am all ears.

Thank you for following up.

 

[Gord Dibben]

I was thinking you were auto-filtering rows based on the QCO's in one column.

It appears as if you want to just filter out cells in one column and not
delete entire rows.

When in Filter mode, the "delete cells" is not available, only "delete row".

Without VBA(macros) I believe you will have to do the deed in two steps.

1. Autofilter that column for QCO then select those cells and "Clear
Contents".

2. Remove Autofilter then Edit>Go To>Special>Blanks. Delete cells>Shift up.

Gord

 

[bullseye]

I am only filtering cells in one column. How difficult is it to learn VBA?
(Because there are a few other mechanical steps I would like to automate as
well.)

And yes, after I filter for QCO in the column,
Edit/GoTo/Special/Blanks/Delete works.

Thank you for the update.

I was thinking you were auto-filtering rows based on the QCO's in one column.

It appears as if you want to just filter out cells in one column and not
delete entire rows.

When in Filter mode, the "delete cells" is not available, only "delete row".

Without VBA(macros) I believe you will have to do the deed in two steps.

1. Autofilter that column for QCO then select those cells and "Clear
Contents".

2. Remove Autofilter then Edit>Go To>Special>Blanks. Delete cells>Shift up.

 

[Gord Dibben]

bullseye

For getting started with macros visit David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Note the links to tutorials and other sites at page bottom.

For your particular problem with the QCO cells.......

Sub delete_QCO()
Dim c As Range
With Columns("A")
Do
Set c = .Find("QCO", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.Delete shift:=xlUp
Loop
End With
End Sub

Copy the code above.

Hit ALT + F11 to go to Visual Basic Editor.'

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module.

Paste the code in there.

File>Save

Close the VBE or hit ALT + Q to return to your workbook.

Tools>Macro>Macros. Select your macro by name and "Run".

Always best to make sure you have a current copy of your workbook before
dinking about.

Gord