Remove columns with all zeros

How can I remove columns that contain all zeros? Is there a macro? I craeted 
a sumif formula to flag columns subtotaling zero but I need a method to 
remove these columns. Thanks
0
Utf
6/1/2010 9:54:03 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
810 Views

Similar Articles

[PageSpeed] 39

Try this:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
    If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
        Columns(i).Delete
    End If
Next
End Sub

-- 
Gary''s Student - gsnu201003


"Nora_GG" wrote:

> How can I remove columns that contain all zeros? Is there a macro? I craeted 
> a sumif formula to flag columns subtotaling zero but I need a method to 
> remove these columns. Thanks
0
Utf
6/1/2010 11:45:01 PM
I would use COUNTIF() instead..

Sub DeleteColumnswithZeros()
Dim lngCol As Long, lngLastCol As Long

lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = lngLastCol To 1 Step -1
If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
WorksheetFunction.CountBlank(Columns(lngCol)) = _
Rows.Count Then Columns(lngCol).Delete
Next
End Sub


-- 
Jacob (MVP - Excel)


"Nora_GG" wrote:

> How can I remove columns that contain all zeros? Is there a macro? I craeted 
> a sumif formula to flag columns subtotaling zero but I need a method to 
> remove these columns. Thanks
0
Utf
6/2/2010 5:09:01 AM
Thank you Gary's Student. I am pretty new to building macros. I created the 
macro below in a separate workbook and then opened it in the workbook I 
needed to apply the macro. Should I have created the macro in the workbook 
that needed the columns removed? Also, did I need to include a range in the 
macro below or was it ok to copy as is? Appreciate the assistance.

"Gary''s Student" wrote:

> Try this:
> 
> Sub RemoveColumns()
> Dim nLastColumn As Long
> Set r = ActiveSheet.UsedRange
> nLastColumn = r.Columns.Count + r.Column - 1
> For i = nLastColumn To 1 Step -1
>     If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
>         Columns(i).Delete
>     End If
> Next
> End Sub
> 
> -- 
> Gary''s Student - gsnu201003
> 
> 
> "Nora_GG" wrote:
> 
> > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > a sumif formula to flag columns subtotaling zero but I need a method to 
> > remove these columns. Thanks
0
Utf
6/2/2010 10:28:24 PM
Thank you Jacob. I am pretty new to building macros. I created the macro 
below in a separate workbook and then opened it in the workbook I needed to 
apply the macro. Should I have created the macro in the workbook that needed 
the columns removed? Also, did I need to include a range in the macro below 
or was it ok to copy as is? Appreciate the assistance.

By the way, I created the macro below by selecting the Macro option under 
Tools.

Thanks again.

"Jacob Skaria" wrote:

> I would use COUNTIF() instead..
> 
> Sub DeleteColumnswithZeros()
> Dim lngCol As Long, lngLastCol As Long
> 
> lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> For lngCol = lngLastCol To 1 Step -1
> If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> WorksheetFunction.CountBlank(Columns(lngCol)) = _
> Rows.Count Then Columns(lngCol).Delete
> Next
> End Sub
> 
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Nora_GG" wrote:
> 
> > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > a sumif formula to flag columns subtotaling zero but I need a method to 
> > remove these columns. Thanks
0
Utf
6/2/2010 10:32:11 PM
=46or a start you would be best off copying and pasting the macro as is =
to a
general module in the workbook to acted upon.

Otherwise you would have to qualify which workbook has the ActiveSheet.

UsedRange should take care of all columns and rows that Excel sees as =
being
in use on the active worksheet.


Gord Dibben  MS Excel MVP

On Wed, 2 Jun 2010 15:28:24 -0700, Nora_GG
<NoraGG@discussions.microsoft.com> wrote:

>Thank you Gary's Student. I am pretty new to building macros. I created =
the=20
>macro below in a separate workbook and then opened it in the workbook I=20
>needed to apply the macro. Should I have created the macro in the =
workbook=20
>that needed the columns removed? Also, did I need to include a range in =
the=20
>macro below or was it ok to copy as is? Appreciate the assistance.
>
>"Gary''s Student" wrote:
>
>> Try this:
>>=20
>> Sub RemoveColumns()
>> Dim nLastColumn As Long
>> Set r =3D ActiveSheet.UsedRange
>> nLastColumn =3D r.Columns.Count + r.Column - 1
>> For i =3D nLastColumn To 1 Step -1
>>     If Application.WorksheetFunction.Sum(Columns(i)) =3D 0 Then
>>         Columns(i).Delete
>>     End If
>> Next
>> End Sub
>>=20
>> --=20
>> Gary''s Student - gsnu201003
>>=20
>>=20
>> "Nora_GG" wrote:
>>=20
>> > How can I remove columns that contain all zeros? Is there a macro? I=
 craeted=20
>> > a sumif formula to flag columns subtotaling zero but I need a method=
 to=20
>> > remove these columns. Thanks

0
Gord
6/2/2010 10:45:18 PM
Hello. I set up teh macro and it removed teh columns with all zeros. It also 
removed columns containing text data. How can I tweak the macro below so it 
ignores columns that contain data such as names, job title, etc.

Thanks again for your asisstance.

"Gary''s Student" wrote:

> Try this:
> 
> Sub RemoveColumns()
> Dim nLastColumn As Long
> Set r = ActiveSheet.UsedRange
> nLastColumn = r.Columns.Count + r.Column - 1
> For i = nLastColumn To 1 Step -1
>     If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
>         Columns(i).Delete
>     End If
> Next
> End Sub
> 
> -- 
> Gary''s Student - gsnu201003
> 
> 
> "Nora_GG" wrote:
> 
> > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > a sumif formula to flag columns subtotaling zero but I need a method to 
> > remove these columns. Thanks
0
Utf
6/3/2010 12:52:31 AM
If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security). 
--From workbook launch VBE using short-key Alt+F11. 
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. 
--Run macro from Tools|Macro|Run <selected macro()>


--I created the macro below in a separate workbook and then opened it in the 
workbook I needed to apply the macro. Should I have created the macro in the 
workbook that needed the columns removed? 

No need; the workbook in which you need to delete the columns should be the 
active workbook..

--Also, did I need to include a range in the macro below or was it ok to 
copy as is? Appreciate the assistance.

No need; the macro identifies the last column with values and check for zero 
cells upto that column.

-- 
Jacob (MVP - Excel)


"Nora_GG" wrote:

> Thank you Jacob. I am pretty new to building macros. I created the macro 
> below in a separate workbook and then opened it in the workbook I needed to 
> apply the macro. Should I have created the macro in the workbook that needed 
> the columns removed? Also, did I need to include a range in the macro below 
> or was it ok to copy as is? Appreciate the assistance.
> 
> By the way, I created the macro below by selecting the Macro option under 
> Tools.
> 
> Thanks again.
> 
> "Jacob Skaria" wrote:
> 
> > I would use COUNTIF() instead..
> > 
> > Sub DeleteColumnswithZeros()
> > Dim lngCol As Long, lngLastCol As Long
> > 
> > lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> > SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> > For lngCol = lngLastCol To 1 Step -1
> > If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> > WorksheetFunction.CountBlank(Columns(lngCol)) = _
> > Rows.Count Then Columns(lngCol).Delete
> > Next
> > End Sub
> > 
> > 
> > -- 
> > Jacob (MVP - Excel)
> > 
> > 
> > "Nora_GG" wrote:
> > 
> > > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > > a sumif formula to flag columns subtotaling zero but I need a method to 
> > > remove these columns. Thanks
0
Utf
6/3/2010 5:01:24 AM
Thanks again Jacob. I applied the macro below and it didn't do anything. I 
also applied the macro Gord Dibben suggested and it removed all columns 
totaling zero. The only problem is that it also removed columns containing 
text such as Names, Job Title, etc.

Can you suggest how I can tweak the macro below so it disregards columns 
with text and dates and looks at only the numbers?

Again really apperciate the assistance.


Sub RemoveColumns() 
Dim nLastColumn As Long 
Set r = ActiveSheet.UsedRange 
nLastColumn = r.Columns.Count + r.Column - 1 
For i = nLastColumn To 1 Step -1 
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then 
Columns(i).Delete 
End If 
Next 
End Sub 


"Jacob Skaria" wrote:

> If you are new to macros..
> 
> --Set the Security level to low/medium in (Tools|Macro|Security). 
> --From workbook launch VBE using short-key Alt+F11. 
> --From menu 'Insert' a module and paste the below code.
> --Get back to Workbook. 
> --Run macro from Tools|Macro|Run <selected macro()>
> 
> 
> --I created the macro below in a separate workbook and then opened it in the 
> workbook I needed to apply the macro. Should I have created the macro in the 
> workbook that needed the columns removed? 
> 
> No need; the workbook in which you need to delete the columns should be the 
> active workbook..
> 
> --Also, did I need to include a range in the macro below or was it ok to 
> copy as is? Appreciate the assistance.
> 
> No need; the macro identifies the last column with values and check for zero 
> cells upto that column.
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Nora_GG" wrote:
> 
> > Thank you Jacob. I am pretty new to building macros. I created the macro 
> > below in a separate workbook and then opened it in the workbook I needed to 
> > apply the macro. Should I have created the macro in the workbook that needed 
> > the columns removed? Also, did I need to include a range in the macro below 
> > or was it ok to copy as is? Appreciate the assistance.
> > 
> > By the way, I created the macro below by selecting the Macro option under 
> > Tools.
> > 
> > Thanks again.
> > 
> > "Jacob Skaria" wrote:
> > 
> > > I would use COUNTIF() instead..
> > > 
> > > Sub DeleteColumnswithZeros()
> > > Dim lngCol As Long, lngLastCol As Long
> > > 
> > > lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> > > SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> > > For lngCol = lngLastCol To 1 Step -1
> > > If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> > > WorksheetFunction.CountBlank(Columns(lngCol)) = _
> > > Rows.Count Then Columns(lngCol).Delete
> > > Next
> > > End Sub
> > > 
> > > 
> > > -- 
> > > Jacob (MVP - Excel)
> > > 
> > > 
> > > "Nora_GG" wrote:
> > > 
> > > > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > > > a sumif formula to flag columns subtotaling zero but I need a method to 
> > > > remove these columns. Thanks
0
Utf
6/3/2010 5:13:51 PM
Gord Dibben never suggested a macro.

Gord told you where to store the macro that Gary's Student had posted for
you.


Gord Dibben  MS Excel MVP

On Thu, 3 Jun 2010 10:13:51 -0700, Nora_GG
<NoraGG@discussions.microsoft.com> wrote:

>Thanks again Jacob. I applied the macro below and it didn't do anything.=
 I=20
>also applied the macro Gord Dibben suggested and it removed all columns=20
>totaling zero. The only problem is that it also removed columns =
containing=20
>text such as Names, Job Title, etc.
>
>Can you suggest how I can tweak the macro below so it disregards columns=
=20
>with text and dates and looks at only the numbers?
>
>Again really apperciate the assistance.
>
>
>Sub RemoveColumns()=20
>Dim nLastColumn As Long=20
>Set r =3D ActiveSheet.UsedRange=20
>nLastColumn =3D r.Columns.Count + r.Column - 1=20
>For i =3D nLastColumn To 1 Step -1=20
>If Application.WorksheetFunction.Sum(Columns(i)) =3D 0 Then=20
>Columns(i).Delete=20
>End If=20
>Next=20
>End Sub=20
>
>
>"Jacob Skaria" wrote:
>
>> If you are new to macros..
>>=20
>> --Set the Security level to low/medium in (Tools|Macro|Security).=20
>> --From workbook launch VBE using short-key Alt+F11.=20
>> --From menu 'Insert' a module and paste the below code.
>> --Get back to Workbook.=20
>> --Run macro from Tools|Macro|Run <selected macro()>
>>=20
>>=20
>> --I created the macro below in a separate workbook and then opened it =
in the=20
>> workbook I needed to apply the macro. Should I have created the macro =
in the=20
>> workbook that needed the columns removed?=20
>>=20
>> No need; the workbook in which you need to delete the columns should =
be the=20
>> active workbook..
>>=20
>> --Also, did I need to include a range in the macro below or was it ok =
to=20
>> copy as is? Appreciate the assistance.
>>=20
>> No need; the macro identifies the last column with values and check =
for zero=20
>> cells upto that column.
>>=20
>> --=20
>> Jacob (MVP - Excel)
>>=20
>>=20
>> "Nora_GG" wrote:
>>=20
>> > Thank you Jacob. I am pretty new to building macros. I created the =
macro=20
>> > below in a separate workbook and then opened it in the workbook I =
needed to=20
>> > apply the macro. Should I have created the macro in the workbook =
that needed=20
>> > the columns removed? Also, did I need to include a range in the =
macro below=20
>> > or was it ok to copy as is? Appreciate the assistance.
>> >=20
>> > By the way, I created the macro below by selecting the Macro option =
under=20
>> > Tools.
>> >=20
>> > Thanks again.
>> >=20
>> > "Jacob Skaria" wrote:
>> >=20
>> > > I would use COUNTIF() instead..
>> > >=20
>> > > Sub DeleteColumnswithZeros()
>> > > Dim lngCol As Long, lngLastCol As Long
>> > >=20
>> > > lngLastCol =3D ActiveSheet.Cells.Find(What:=3D"*", _
>> > > SearchDirection:=3DxlPrevious, SearchOrder:=3DxlByColumns).Column
>> > > For lngCol =3D lngLastCol To 1 Step -1
>> > > If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
>> > > WorksheetFunction.CountBlank(Columns(lngCol)) =3D _
>> > > Rows.Count Then Columns(lngCol).Delete
>> > > Next
>> > > End Sub
>> > >=20
>> > >=20
>> > > --=20
>> > > Jacob (MVP - Excel)
>> > >=20
>> > >=20
>> > > "Nora_GG" wrote:
>> > >=20
>> > > > How can I remove columns that contain all zeros? Is there a =
macro? I craeted=20
>> > > > a sumif formula to flag columns subtotaling zero but I need a =
method to=20
>> > > > remove these columns. Thanks

0
Gord
6/3/2010 10:18:54 PM
My apologies Gord Dibben.  

"Gord Dibben" wrote:

> Gord Dibben never suggested a macro.
> 
> Gord told you where to store the macro that Gary's Student had posted for
> you.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Thu, 3 Jun 2010 10:13:51 -0700, Nora_GG
> <NoraGG@discussions.microsoft.com> wrote:
> 
> >Thanks again Jacob. I applied the macro below and it didn't do anything. I 
> >also applied the macro Gord Dibben suggested and it removed all columns 
> >totaling zero. The only problem is that it also removed columns containing 
> >text such as Names, Job Title, etc.
> >
> >Can you suggest how I can tweak the macro below so it disregards columns 
> >with text and dates and looks at only the numbers?
> >
> >Again really apperciate the assistance.
> >
> >
> >Sub RemoveColumns() 
> >Dim nLastColumn As Long 
> >Set r = ActiveSheet.UsedRange 
> >nLastColumn = r.Columns.Count + r.Column - 1 
> >For i = nLastColumn To 1 Step -1 
> >If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then 
> >Columns(i).Delete 
> >End If 
> >Next 
> >End Sub 
> >
> >
> >"Jacob Skaria" wrote:
> >
> >> If you are new to macros..
> >> 
> >> --Set the Security level to low/medium in (Tools|Macro|Security). 
> >> --From workbook launch VBE using short-key Alt+F11. 
> >> --From menu 'Insert' a module and paste the below code.
> >> --Get back to Workbook. 
> >> --Run macro from Tools|Macro|Run <selected macro()>
> >> 
> >> 
> >> --I created the macro below in a separate workbook and then opened it in the 
> >> workbook I needed to apply the macro. Should I have created the macro in the 
> >> workbook that needed the columns removed? 
> >> 
> >> No need; the workbook in which you need to delete the columns should be the 
> >> active workbook..
> >> 
> >> --Also, did I need to include a range in the macro below or was it ok to 
> >> copy as is? Appreciate the assistance.
> >> 
> >> No need; the macro identifies the last column with values and check for zero 
> >> cells upto that column.
> >> 
> >> -- 
> >> Jacob (MVP - Excel)
> >> 
> >> 
> >> "Nora_GG" wrote:
> >> 
> >> > Thank you Jacob. I am pretty new to building macros. I created the macro 
> >> > below in a separate workbook and then opened it in the workbook I needed to 
> >> > apply the macro. Should I have created the macro in the workbook that needed 
> >> > the columns removed? Also, did I need to include a range in the macro below 
> >> > or was it ok to copy as is? Appreciate the assistance.
> >> > 
> >> > By the way, I created the macro below by selecting the Macro option under 
> >> > Tools.
> >> > 
> >> > Thanks again.
> >> > 
> >> > "Jacob Skaria" wrote:
> >> > 
> >> > > I would use COUNTIF() instead..
> >> > > 
> >> > > Sub DeleteColumnswithZeros()
> >> > > Dim lngCol As Long, lngLastCol As Long
> >> > > 
> >> > > lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> >> > > SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> >> > > For lngCol = lngLastCol To 1 Step -1
> >> > > If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> >> > > WorksheetFunction.CountBlank(Columns(lngCol)) = _
> >> > > Rows.Count Then Columns(lngCol).Delete
> >> > > Next
> >> > > End Sub
> >> > > 
> >> > > 
> >> > > -- 
> >> > > Jacob (MVP - Excel)
> >> > > 
> >> > > 
> >> > > "Nora_GG" wrote:
> >> > > 
> >> > > > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> >> > > > a sumif formula to flag columns subtotaling zero but I need a method to 
> >> > > > remove these columns. Thanks
> 
> .
> 
0
Utf
6/3/2010 11:46:29 PM
Hi Jacob, I received additional assistance from Gary's Student and was able 
to tweak the macro to provide me with the desired results. Thank you again.

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
    i1 = Application.WorksheetFunction.Sum(Columns(i))
    i2 = Application.WorksheetFunction.Count(Columns(i))
    If i1 = 0 And i2 <> 0 Then
    Columns(i).Delete
    End If
Next
End Sub

 

"Jacob Skaria" wrote:

> If you are new to macros..
> 
> --Set the Security level to low/medium in (Tools|Macro|Security). 
> --From workbook launch VBE using short-key Alt+F11. 
> --From menu 'Insert' a module and paste the below code.
> --Get back to Workbook. 
> --Run macro from Tools|Macro|Run <selected macro()>
> 
> 
> --I created the macro below in a separate workbook and then opened it in the 
> workbook I needed to apply the macro. Should I have created the macro in the 
> workbook that needed the columns removed? 
> 
> No need; the workbook in which you need to delete the columns should be the 
> active workbook..
> 
> --Also, did I need to include a range in the macro below or was it ok to 
> copy as is? Appreciate the assistance.
> 
> No need; the macro identifies the last column with values and check for zero 
> cells upto that column.
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Nora_GG" wrote:
> 
> > Thank you Jacob. I am pretty new to building macros. I created the macro 
> > below in a separate workbook and then opened it in the workbook I needed to 
> > apply the macro. Should I have created the macro in the workbook that needed 
> > the columns removed? Also, did I need to include a range in the macro below 
> > or was it ok to copy as is? Appreciate the assistance.
> > 
> > By the way, I created the macro below by selecting the Macro option under 
> > Tools.
> > 
> > Thanks again.
> > 
> > "Jacob Skaria" wrote:
> > 
> > > I would use COUNTIF() instead..
> > > 
> > > Sub DeleteColumnswithZeros()
> > > Dim lngCol As Long, lngLastCol As Long
> > > 
> > > lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> > > SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> > > For lngCol = lngLastCol To 1 Step -1
> > > If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> > > WorksheetFunction.CountBlank(Columns(lngCol)) = _
> > > Rows.Count Then Columns(lngCol).Delete
> > > Next
> > > End Sub
> > > 
> > > 
> > > -- 
> > > Jacob (MVP - Excel)
> > > 
> > > 
> > > "Nora_GG" wrote:
> > > 
> > > > How can I remove columns that contain all zeros? Is there a macro? I craeted 
> > > > a sumif formula to flag columns subtotaling zero but I need a method to 
> > > > remove these columns. Thanks
0
Utf
6/3/2010 11:49:31 PM
Reply:

Similar Artilces:

Changing font case in a selected column
Probably an easy one for you experts but I am very frustrated! I have copied data from one worksheet to another and to tidy it up I want to change the font from sentence case e.g. Hume to upper case e.g. HUME. How do I do it? Many Thanks Paul With Hume in A1, put this in B1 =UPPER(A1) it will display as HUME....... Note that =PROPER(B1) in cell C1 will turn it back to Hume....... Vaya con Dios, Chuck, CABGx3 "PAUL HUME" <paul.c.hume@jobcentreplus.gov.uk> wrote in message news:0a4101c34608$450edaf0$a501280a@phx.gbl... > Probably an easy one for you experts ...

Label Columns
I hope I can explain this easily. I am using an excel document with two columns (A and B). How can I print the labels (which are two columned) so that Column A from excel is to the left of column B when printed onto labels. I am trying to make it easy on myself when I go to peel the labels that I can peel the left label and then place the right label with it. It is different information and they cannot be on the same label. Is there a way to do this? Well to start off with printing labels is never easy from Excel, though you would thing nothing could be simpler. But Microsoft d...

join columns, keep both values
I have a multicolumn database with text entries. I want to join the cells in Col. B with the cells in Col.C and keep all the text. For instance, B3 may be "1N", C3 may be "4W". I want the combined column to read "1N 4W". How do I do this?!? TNX. -- FourPenny ------------------------------------------------------------------------ FourPenny's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23864 View this thread: http://www.excelforum.com/showthread.php?threadid=375062 Try this: =B3&" "&C3 -->"1N 4W&q...

Move a column up or down
Sometimes i need to move a specfic column one line up or down, m quetios is how ro do it? of course i know how to insert o delete column but the column itself one line up o down tks mloo5 -- MLOO5 ----------------------------------------------------------------------- MLOO55's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38564 I assume you mean you want to move a ROW up or down? You can copy the row and insert it back where you want it. "MLOO55" <MLOO55.1rv1uc_1120842307.6...

getting more columns and data onto one page in Crystal
Hello: Since you can only place so much data onto a "piece of paper", likely the answer is "no". But, even after choosing "Landscape" in Crytal (10.0), there's not a way of getting as many columns of data onto a report as I want--is there? Ideally, I'd like to get 14 columns on there but am running out of room on the right. Also, I have placed a cross-tab on my report that prints on multiple pages instead of just one. Of course, since the cross-tab has 83 rows of data, my guess is that there is nothing that I can do about this either. Is there? ...

Idetnifying repeats in a column
Can anyone help? If I have a column of 10 digit numbers, is there a function which can identify when a number is repeated e.g. A B 1 Number Repeat? 2 1234567890 Yes 3 2345678901 No 4 3456789012 No 5 1234567890 Yes 6 4567890123 No So I need a function entered in column B which searches down the entire column A to look for an identical number and if it finds one, it puts ...

In Excel, COLUMN HEADERS ARE NUMERICAL
I don't know what I've done, but my columns are numerical 1, 2, 3, etc not the typical Column A, B, C. Also, my functions all read something like this: R[-1]C+R[24]C[2] How can I get my spreadsheets back to normal? tools>options>general>r1c1 -- Don Guillett SalesAid Software donaldb@281.com "dstach11" <dstach11@discussions.microsoft.com> wrote in message news:52764F92-BC69-4193-BA78-65850E8EB8E2@microsoft.com... > I don't know what I've done, but my columns are numerical 1, 2, 3, etc not > the typical Column A, B, C. Also, my functions all...

dropdownlist two columns
Hi, I want a dropdown list that refers to two (hidden) columns. Column being an ID-number, column B being the description. Eventually I need the ID-number in the cell with the dropdownlist, bu the user needs the description to decide. Via Data-Validation I can only refer to one column. or not? thanks, spoef -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.contextures.com/excelfiles.html look for Data Validation "Columns" -- Regards Frank Kabel Frankfurt, Germany > Hi, > > I want a dropdown list that refers to two (hidden) columns....

Remove decimal places from Discount Percent
I am modifying a blank invoice form with Report Writer in GP 10. I have created an integer calculated field that adds the Markdown Percent and the Trade Discount Percent for each line item. I have tried to format the field to remove the .00 from the displayed discount and only display the whole numbers (20%). I am not using the field for any other calculations. Anyone know how? You could change the calculated field format on the report layout to use DLR_RB0_STR. Just double-click on your field on the Report Layout window, click on the ellipses button next to format and chose from there...

How do I remove the outline protection in Excel sheet?
When I use the sheet protection feature of Excel, I can not unprotect the outlining feature of the spreadsheet. I would like to protect some cells in the spreadsheet, but not disable the outlining expanding and collapsing feature. I am not asking to edit the outline. I just want to use the outlining features as a means to navigate large sheets. Currently, I have to unprotect the sheet in order to expand or collapse the outline. Is there a way to view a protected sheet with the outlining features enables? If you already have the outline applied, you can protect the worksheet in code...

Insuficient Key Column Information
Hi, When I try create a new button on my POS touch screen SO Manager (version 2.0) returns an error message "Insuficient Key Column Information" and refuses to allow a new button to be created. Can anyone interpret this message for me and let me know how to fix it so I can create a new button. The new button is a standard button, not a custom one. I just want to put the reprint button on my custom buttons menu. Thanks Dan Levin ...

Removing Downloaded "E" flag from transactions copied or added to Bills & Deposits
I have a couple of transactions that were downloaded from the bank that I have added to Bills & Deposits. However, each time the bill comes due and is added to the register, the "E" shows up in the cleared column and "Downloaded" appears in the transaction detail. The same is true if you wish to copy a transaction to another account. There appears to be no way to clear this indicator. Am I missing something? Thanks for any help. Bill, If I understand your correctly, you added the payments to Bills and Deposits from your account register after they were downloaded f...

Counting Three Different Words In One Column
H ithere, Can someone please help me? I have a column which can only have option (due to validation), these are:- New Overdue Updated Closed I would like to count the number of times each one has been entered. Each count would be in a different cell on a different worksheet. have tried searching this forum and found answers similar to what i' looking for but i could not get any of them to work for some reason? Thanks very much for your time. Swmasso -- Message posted from http://www.ExcelForum.com Hi you need to use the countif function for this so if your range is ...

Removed/blocked attachments
When Outlook blocks access to an attachment, is it possible to access these files another way? Win2k3, Exch2k3, Outlook2k3 See http://support.microsoft.com/?id=829982 -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Eirik" <bla@bla.bla> wrote in message news:O3$4DljoEHA.4032@TK2MSFTNGP15.phx.gbl... > When Outlook blocks access to an attachment, is it possible to access these > files another way? > Win2k3, Exch2k3, Outlook2k3 > > via OWA "Eirik" <bla@bla.bla> wrote in message news:O3$4...

Column Alias
In the query below, I can sucessfully return different recordsets based on a parameter. The problem is that I need to change the "Month, Month_Name" and "Quarter, Quarter_Name" headers to something generic like "vDate, vDate_Name" .... Any help would be appreciated. SELECT NON EMPTY {[Measures].[Clm Amnt], [Measures].[Denial Detail Count]} ON COLUMNS, NON EMPTY{ORDER({(iif(@TimeSlice="Quarter",[DenialPostongTimeData].[Quarter].[Quarter],[DenialPostongTimeData].[Month].[Month]) AS [Date],iif(@TimeSlice="Quarter",[DenialPos...

returning more columns with vlookup?
Hi all by default vlookup returns 1 column. Is there a way to return more than 1 (not necessarily adjacent) columns? example instead of =VLOOKUP(38, A2:L10, 3, FALSE) using something like =VLOOKUP(38, A2:L10, (3,7,9), FALSE) that would return cells in columns 3, 7 and 9 ? Thanks! Maybe not exactly what you require, but it might help you: http://www.cpearson.com/excel/TablesAndLookups.aspx Look under the heading "Returning A Row Or Column From A Table" -- Kind regards, Niek Otten Microsoft MVP - Excel "eglea" <eglea@nospam.nospam> wrote in message news:...

Unable to remove exchange 2000
Hi all, I tried to remove exchange 2000 from one exchange 2000 server but I am not able to. When I select action "remove", it pops up the remove action can not be assigned as one or more users currently use a mailbox store on this server.. I double check the mailboxes and only SMTP, system attendent and systemMailboxes are on this server. Can anyone help me to remove exchange 2000 from this server? Price of a pint says this: http://hellomate.typepad.com/exchange/2004/02/having_trouble_.html is your man. Thank you and thank you all. "Mark Arnold [MVP]" wrote: &...

macro to hide columns with zero values?
I'm relatively new to VBA and need to create a macro to hide columns with zero values. The workbook has 36 sheets, each sheet has columns B:CR and 202 rows. Row 202 is the sum of the respective column. I'd like to run the macro in a separate workbook so I can use it for other similar workbook situations. Any suggestions would be greatly appreciated! I believe the code below will work for you. It will examine all used cells on the row on a selected sheet where a cell is selected and hide all columns with an empty cell or a zero value cell on that row. In your example, ...

Removing file extensions on e-mail
How do I remove the file extension (i.e. xls or xlsx) when e-mailing an Excel workbook from directly within the workbook instead of attaching the workbook to an e-mail? Why ever would you want to do that? John S wrote: > How do I remove the file extension (i.e. xls or xlsx) when e-mailing an Excel > workbook from directly within the workbook instead of attaching the workbook > to an e-mail? ...

Customer Relationship
CRM 3.0 If a user opens up the Account or Contact entity, the Relatinoships icon in the left menu is available. When clicking the Relationships icon, a view of existing Customer Relationships is displayed. Our implementation shows 4 columns (as I believe everyones' does): Party 1, Role 1, Role 2, Party 2. A while back, users had the ability to click on those column headers to sort the records how they wanted. But for some reason, the application today has no default sort order and does not allow the selection of a column header at all. The only thing one of our end users could...

unable to remove exchange 2003
I'm tried to uninstall exchange 2003 server, but i recieved the message indicates that you must disable or uninstall the mailboxes firest. i' only have 2 mailboxes in the storage group, one for SMTP and the other for system attendant and both can't be deleted, and i unable to uninstall the exchange. i need the solution for uninstall exchange. thanks Try this: http://support.microsoft.com/?id=555147 -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "RG" <RG@discussions.microsoft.com> wrote in message n...

After removing accounts in Server Management, Exch System Manager still shows accounts.
This is SBS 2003 Premium. I needed to remove two accounts. I did it in Server Managment -> Users. Right click and Remove User. However, they still show up in the Exchange System Manager. When I click on them it says: No Properties are available on this object. So...never having done this before: Purge? And I am assuming Purging does not affect anything else... Thanks On Tue, 24 Apr 2007 14:43:03 -0500, "Purtech" <mikek(remove)@hlit.net> wrote: >This is SBS 2003 Premium. > >I needed to remove two accounts. I did it in Server Managment -> Users. >Rig...

Zero Values on Charts
I am currently developing a spreadsheet which will allow the Actuals Forecast and Budgets of a financial year to be viewed. The data will spread across financial periods i.e. P1 - P12 and b their nature the forecast and budget data will be filled in for al periods. My problem is that I intend to add the actuals at the end of eac month, so that they can be viewed against budget and forecast. However I don't want to have the line returning to 0 when the actuals fo certain periods haven't been entered. Does anyone know how you can have a line graph that looks at a rang for actuals (t...

Wrap text on Column "Task name/Description" of Timesheet View page
Dear all, Is there any way to wrap text on Column "Task name/Description" of Timesheet View page? Is it possible through javascript or through the relative aspx page? I'll appreciate any help on that. Kind regards, Konstantinos Christodoulakis Senior Project Manager Hi again, unfortunately that is natively not possible in 2007. You may be able to develop this functionality and may want to ask the question at the project developer user forum. thanks Marc [MVP] "Konstantinos Christodoulakis" <KonstantinosChristodoulakis@discussions.microso...

auto return to column A but of next row
Is there an easy way to auto return to column A but of next row. I would like to be able to do this automatically say when column 87 is reached. I could just arrow down and hit home but there must be a way to automate this. I am currently in 2003 but will be moving to 2007 in a few months. Thanks in advance! -- herbie000 Start in A1........Tab, Tab, Tab across to column 87. Leave column 87 by hitting ENTER key to go back to A2 Or lock all columns from 88 onward, unlock 1 to 87 Protect the worksheet and use the Tab key all the way across. Gord Dibben MS Exc...