deleting every other cell in a column

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


0
9/13/2004 3:38:10 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
423 Views

Similar Articles

[PageSpeed] 18

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

--
Regards
Frank Kabel
Frankfurt, Germany

"bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
>
> 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
>
>

0
frank.kabel (11126)
9/13/2004 4:35:49 PM
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" <frank.kabel@freenet.de> wrote in message
news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> Hi
> try applying a filter first ('Data - Filter') and delete the filtered
> rows/cells.
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> >
> > 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
> >
> >
>


0
9/13/2004 5:58:59 PM
Hi
not without using VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
Newsbeitrag news:Ddl1d.35918$Ot3.13627@twister.nyc.rr.com...
> 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" <frank.kabel@freenet.de> wrote in message
> news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> > Hi
> > try applying a filter first ('Data - Filter') and delete the
filtered
> > rows/cells.
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> > Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> > >
> > > 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
> > >
> > >
> >
>
>

0
frank.kabel (11126)
9/13/2004 6:02:27 PM
Thank you.




"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:ekWlLvbmEHA.2612@TK2MSFTNGP15.phx.gbl...
> Hi
> not without using VBA
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> Newsbeitrag news:Ddl1d.35918$Ot3.13627@twister.nyc.rr.com...
> > 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" <frank.kabel@freenet.de> wrote in message
> > news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> > > Hi
> > > try applying a filter first ('Data - Filter') and delete the
> filtered
> > > rows/cells.
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> > > Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> > > >
> > > > 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
> > > >
> > > >
> > >
> >
> >
>


0
9/13/2004 6:28:52 PM
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.



bullseye wrote:
> 
> 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" <frank.kabel@freenet.de> wrote in message
> news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> > Hi
> > try applying a filter first ('Data - Filter') and delete the filtered
> > rows/cells.
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> > Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> > >
> > > 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
> > >
> > >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/13/2004 10:05:26 PM
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

On Mon, 13 Sep 2004 17:58:59 GMT, "bullseye"
<bullseye35@deletetheextrastuff@excite.com> wrote:

>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" <frank.kabel@freenet.de> wrote in message
>news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
>> Hi
>> try applying a filter first ('Data - Filter') and delete the filtered
>> rows/cells.
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
>> Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
>> >
>> > 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
>> >
>> >
>>
>

0
Gord
9/13/2004 11:56:16 PM
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" <gorddibbATshawDOTca> wrote in message
news:3rcck0t3hp9l2bggtb9gr9csb961l72f9k@4ax.com...
> 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
>
> On Mon, 13 Sep 2004 17:58:59 GMT, "bullseye"
> <bullseye35@deletetheextrastuff@excite.com> wrote:
>
> >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" <frank.kabel@freenet.de> wrote in message
> >news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> >> Hi
> >> try applying a filter first ('Data - Filter') and delete the filtered
> >> rows/cells.
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> >> Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> >> >
> >> > 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
> >> >
> >> >
> >>
> >
>


0
9/14/2004 4:44:45 AM
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

On Mon, 13 Sep 2004 16:56:16 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>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
>
>On Mon, 13 Sep 2004 17:58:59 GMT, "bullseye"
><bullseye35@deletetheextrastuff@excite.com> wrote:
>
>>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" <frank.kabel@freenet.de> wrote in message
>>news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
>>> Hi
>>> try applying a filter first ('Data - Filter') and delete the filtered
>>> rows/cells.
>>>
>>> --
>>> Regards
>>> Frank Kabel
>>> Frankfurt, Germany
>>>
>>> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
>>> Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
>>> >
>>> > 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
>>> >
>>> >
>>>
>>

0
Gord
9/14/2004 3:35:44 PM
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.


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:253ek0p4qchu0hg2ri6tk94m3ktfp3458f@4ax.com...
> 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
>
> On Mon, 13 Sep 2004 16:56:16 -0700, Gord Dibben <gorddibbATshawDOTca>
wrote:
>
> >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
> >
> >On Mon, 13 Sep 2004 17:58:59 GMT, "bullseye"
> ><bullseye35@deletetheextrastuff@excite.com> wrote:
> >
> >>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" <frank.kabel@freenet.de> wrote in message
> >>news:%23rBcx%23amEHA.1656@TK2MSFTNGP09.phx.gbl...
> >>> Hi
> >>> try applying a filter first ('Data - Filter') and delete the filtered
> >>> rows/cells.
> >>>
> >>> --
> >>> Regards
> >>> Frank Kabel
> >>> Frankfurt, Germany
> >>>
> >>> "bullseye" <bullseye35@deletetheextrastuff@excite.com> schrieb im
> >>> Newsbeitrag news:C9j1d.35912$Ot3.4923@twister.nyc.rr.com...
> >>> >
> >>> > 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
> >>> >
> >>> >
> >>>
> >>
>


0
9/14/2004 6:32:39 PM
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


On Tue, 14 Sep 2004 18:32:39 GMT, "bullseye"
<bullseye35@deletetheextrastuff@excite.com> wrote:

>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.

0
Gord
9/14/2004 7:26:06 PM
Reply:

Similar Artilces:

created new list, disabled default columns, can't modify
Hi, I created a new list using the tasks template. I've created my own columns and disabled the default ones. Just found out that doing this, I cannot modify anything. Normally, when going into a list, you can click on the default column (title I think) to modify or to get into the information that's there for modifications but because I am only using customized columns, I can't modify anything even though I have full control so it's not a permission issue. Normally what I do is to rename the default "title" list to something else I use and people are...

Import external data-text files but placed in the next column rather than next empty row
I want to import text files in to one worksheet. The text files are sent daily and all the same format. However the text file names change buy an incremental number. Is it possible to create a macro that imports these text files, ignoring their exact filename but from the same source directory into one worksheet. Can you use wild cards for the name e.g. "********.txt"? At the moment I can manually do this, by selecting the cell below the last record then using the process "Get external Data" and following the wizard which works fine. As soon as I try to create a m...

Select All controls in a worksheet and delete them
I copy information from an internet interface. The information contains controls. (usually check boxes) I would like to select all of these controls on the sheet and delete them. Solve my problem and make my day. Thanks, Hi F5 Special Objects OK Press the Delete key -- Regards Ron de Bruin http://www.rondebruin.nl "rova78" <rova78@discussions.microsoft.com> wrote in message news:173039A1-B4E3-48D2-8AF4-681EA1FA54B8@microsoft.com... >I copy information from an internet interface. The information contains > controls. (usually check boxes) I would like to select ...

Deletion is not working
Hello all, If one of you could help me with this issue, it would be great. I try to delete some duplicated activities in the web version of CRM and it failed everytimes (I have to close my CRM with the ctrl+alt+del to be able to do something). So, we restarted the deletion service on the server and it's still not working. Does one of you have an idea of what's going on? Thank you very much! ...

How Check some cells have been updated
Hi All I want to keep trace some of cells in Row have been updated, the one of column update as today and time ? Does formula can handle this ? eg. A11 or B11 or C11 Updated, the D11 = today + time moonhkt Put this code in your worksheet module. To do this, right click the sheet tab at the bottom of Excel, click View Code, then paste code below into the sheet module. This code will put a time stamp in Col. D if any data is changed in columns A, B, or C. Give it a try. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVa...

Changing values in a row based on a cell in the row.
Hi, I have a little matrix 5R x 6C. All cells within the matrix have data validation in them to restrict the input to "1" or "0". It is OK to have mutiple selections of "1's" in the same row, except if the user happen to select a "1" for the sixth or last cell in the row. If that happens I would like the other five cells in that row to have a value of "0". Something like A B C D E F 1 1 0 0 1 1 0 ok 2 1 0 1 0 0 1 Not ok 3 0 0 0 0 0 1 ok 4 5 -- Casey --------------------------------------...

Formula needed: count if cell CONTAINS certain text
What formula do I need to count the cells that CONTAIN a certain text (<> equal!) e.g. A1 = John A2 = Pieter A3 = William count cells that CONTAIN "i": 2 (A2 and A3) What (set of) formula should I use? Excel Help doesn't give me the answer. Wim On 7 Dec 2005 04:28:36 -0800, "Wim" <eisingspam@iafrica.com> wrote: >What formula do I need to count the cells that CONTAIN a certain text >(<> equal!) >e.g. >A1 = John >A2 = Pieter >A3 = William > >count cells that CONTAIN "i": 2 (A2 and A3) > >What (set of) formula...

VLOOKUP formula appears in the cell I need to see the result in
I am using the VLOOKUP function but when I complete the formula, the formula just appears in the cell I need the result to show up in. Can anyone help? Hi it is possible that the cell has been formatted to text prior to the entry of the formula: click on the cell, choose format / cells - number tab and click on general and click ok. then you might need to press F2 then F9 and enter to get it to work or tools / options / view tab, untick formulas or ensure that your VLOOKUP formula has no space before the =VLOOKUP(........ -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm...

Outlook 2000 DELETES !!! incoming email when reply
At some point recently, when I use "reply" to send message back to someone I know, once message is sent, the original message is nowhere to find, it is permanently deleted. What is going on? HELP The reply I already received (but it didn't help): ========================= Go to the Tool menu and then to the Rules wizard and see if you do not have and rule for that. you can allsow go to Tolls option and look there as well ========================== My reply to the above: ========================= ..... No such a rule (and I didn't set one). In tools-options there was som...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Concatenate 2 columns date & time
Hi, I am working with Excel 2003 and have a ws that I need to combine column B which is a date, with column C which contains the time. No matter how I have formatted column D, I cannot get the date to display correctly. What I have is: B C 05/01/2010 10:55 24/12/2009 09:35 I need column D to show as: 05/01/2010 10:55 24/12/2009 09:35 I have tried several different formulas/formatting in Column D but always end u...

distributing each characters on a cell
how can i distribute the letter in a cell to another cells? example: A1=HELP then get each character B1=H C1=E D1=L E1=P In B1: =MID($A$1,COLUMN(A1),1) Copy to C1:E1 -- Kind regards, Niek Otten Microsoft MVP - Excel "jjuan" <jp4the_clan@yahoo.com> wrote in message news:OFyGM%23$9GHA.4464@TK2MSFTNGP02.phx.gbl... | how can i distribute the letter in a cell to another cells? | example: | | A1=HELP | | then get each character | | B1=H | C1=E | D1=L | E1=P | | | And if you have lots to do in that column, you can select that column data|text to columns Fixed width dr...

substitution operator new & delete provided by MFC?
I tried another memory tracer provided by http://www.fluidstudios.com/, which is called MMGR. Without MFC, MMGR works fine. However, programs using MFC cannot be built because MMGR has some operator new() and delete() functions, which are already defined in MFC library. How can I substitute MFC memory operators to MMGR memory operators? Please reply. Thanks in advance. Bae,Hyun-jik Search your MSDN for "operator new" and read the C/C++ location topics. -- ============ Frank Hickman Microsoft MVP NobleSoft, Inc. ============ Replace the _nosp@m_ with @ to reply. "Ba...

Not able to Paste cells
I have a workbook with multiple sheets. I need to copy several cells from one sheet and paste on other sheets. I copy, then when I go to the other sheet, there is no paste option available. The file and sheets are not protected. The cells are not merged. Paste option is available for copy and paste within a sheet. I am trying to copy and paste values not formulas or hyperlinks. Has anyone seen this before and any idea why this is occurring? Thanks LL The paste option should be available if you are doing what you say. Exactly what else are you doing between the time you copy and t...

deleting/retaining duplicates
I have a spreadsheet with 2 sets of information. I need to be able to filter it so that if there is a duplicate, the 1st set stays and the 2nd set is deleted. If there is no duplicate, the 2nd set will still be displayed. I have tried a couple things but no success yet. Anything I could try? Thanks ...

Addition on "text" cells
Hello everyone, I wonder if this is possible, cell A1 contains the text "P0001", cell A2 contains "P0021", A3 contains "P0041" and so on until "P0581". The values are separated by 20 units. If the cell is numeric, I could just make the formula on A2 as "+A1+20" and copy it down. This will give me the result from cell A2 21, 41, etc. Is there a way to make a formula or something so that I can have "P0001", then "P0021" and so on, on a single column. -- ledzepe -----------------------------------------------------------...

Delete 'non-blank' rows...
Hi. I've pasted data into my worksheet that leaves every other row empty. I need to delete the empty rows but sorting messes up the order, GoTo/Special/Blank won't work and neither will several DeleteEmptyRow macros I've tried. I applied the ISBLANK function to the empty rows and it returned a FALSE value so something unseen is in those rows. No matter, I still need to delete them. Any ideas? Thanks, silas Use autofilter, filter blanks and delete all the rows that get displayed. On Apr 11, 10:03 pm, "silas" <silas.NOSPAMprop...@gmail.com> wrote: > Hi...

converting text to columns
I have a column of dates that have been imported from a CSV file. The format is month year (209, 1112, 1211, etc.). I need to separate the month and year into separate columns using "converting text to columns." However, because some of the numbers are only 3 digits, I can't separate using fix width and it is not delimited. In excel, the column is right justified, but when using the separating tool, it looks left justified. Help. After importing (in Col A) use this in B1 to get the month =MID(A1,1,LEN(A1)-2) and this in C1 to get the year =Right(A1,2) "Linda (Irwi...

Selectively protect cell data
I would like to , when a cell is empty, be able to enter data, but, once data is entered have the cell protected. Is there any way to do this. I'm a novice. thanks for any help. Ken Ken, here is one way, Private Sub Worksheet_Change(ByVal Target As Range) '******unlock all cells in the range first********* Dim MyRange As Range Const Password = "123" '**Change password here** Set MyRange = Intersect(Range("A1:B10"), Target) '**change range here** If Not MyRange Is Nothing Then Unprotect Password:=Password MyRange.Lock...

Selecting a cell according to the cell value
Hello All VB expert Need help with a Macro. I would like to be able to select a cell according to a variable in a cell. For example: Cell A1 “31/04/2010”. Cell A3 = 31/05/2010 Cell A4 = 30/06/2010 Cell A5 = 31/07/2010 Cell A6 = 31/04/2010 In this case when I run the Marco, it will select Cell A6 because it is the same value to Cell A1. And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the same value to Cell A1 If you need more information or my explanation is not clear please let me know. Thank you very much for your help. Harn Se...

Autofill cells based on formula ?
I am using Excel 97. I have two columns. The user enters their numbers into column A and column B. When the user enters their numbers in Column A and B then goes to the next row, Column C is automatically filled based on a formula. Can this be done? If I understand correctly, yes, values in A1 and B1 can easily return a result in C1. You need to be more specitic in what you are trying to achieve. "Rich" <richmarin@earthlink.net> wrote in message news:46e4ef1a.0311161755.19caad43@posting.google.com... > I am using Excel 97. > > I have two columns. The user enters the...

Can't delete category
When I try to delete a category, which has a subcategory, and a subcategory of the subcategory, I get the following error msg: "when you delete a category or classification class, all of the subcategories or subclasses are also deleted. You may want to make sure you don't need the subcategories or subclasses before deleting them" However, gives me the same message when I try to delete the subcategory. If I try a move and delete, Money abruptly closes. How do I delete this category? thanks ...

Display a blank cell when sum is zero
How do I make a cell display a blank instead of a "0" or a "-" when the sum of a formula is zero? Hi, Try going to [Tools] --> Options Under the tab "View" Look at the Window option and uncheck Zero Values Rockee Freema -- Message posted from http://www.ExcelForum.com Hi Charlymon! Example: =IF(SUM(A1:Z1)=0,"",SUM(A1:Z1)) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "charlymon" <charlymon@yahoo...

need a function or property to extract the hyperlink from a cell
I copy a list of hyperlinks with friendly names into a spreadsheet. However, I want to extract the underlying hyperlinks into their own cells. Is there a function or a property I can use in a macro to address the hyperlink behind the friendly name? Thx, J Nevermind, found a way. Thx "J" <jayerk@gmail.com> wrote in message news:94BA463B-BAF0-425C-A6A0-ADA55A1F1984@microsoft.com... > I copy a list of hyperlinks with friendly names into a spreadsheet. > However, I want to extract the underlying hyperlinks into their own cells. > Is there a function or a prope...

conditional format of adjacent cell with different range and crite
Well I have to Conditional format a range of values of range of string character. For Eg. Column A contains Weekdays, 5 Mondays,3 Tuesdays, 2 Wednesday, 7 Thursday, 2 Friday, 3 Saturday and 6 Sunday in Column A. With having range of values from 30 to 100 in the adjacent cells of Column A. Col A Col B Sunday 32 Sunday 65 Sunday 52 Sunday 69 Sunday 45 Sunday 85 Monday 63 Monday 25 Monday 36 Monday 41 Monday 48 Tuesday 95 Tuesday 74 Tuesday 56 Wednesday 15 Wednesday 45 Thursday 49 Thursday 87 Thursday 16 Thursday 53 Thursday 23 Thursday 29 Thursday 21 Friday 54 Friday 63 Saturday 45 Saturd...