Deleting blank rows with formulas in them
I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets.
|
0 |
|
Utf
4/30/2010 3:04:02 PM
excel.misc
78881 articles. 
5 followers.
7 Replies
884 Views
Hi, Which columns are we looking at in these rows, all of them? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: > I have a spreadsheet that takes data from one worksheet and moves it to > several worksheets, depending on a certain value. There are several > worksheets where the data has been moved, based on a macro and formula I > wrote. > There are several rows that will always be blank, however a formula appears > in those rows. Is there a way to create a macro that will look at all the > "named" sheets individually and find any rows from 8-300 that have a formula > in them, but do not contain actual data. > For instance, worksheet NEO has formulas that feed off another sheet from > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank > but contain a formula. How can that e deleted. > Same thing with worksheet OEN, except data is present in rows 8-100, with > 101-300 being blank but containint formulas. > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:14:01 PM
You can try out the below macro. 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()>
Namedrange "SheetNames"..Change to suit
Sub DeleteRows()
Dim ws As Worksheet, cell As Range, lngRow As Long
For Each cell In Range("SheetNames")
Set ws = Sheets(cell.Text)
For lngRow = 24 To 8 Step -1
If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
Then ws.Rows(lngRow).Delete
Next
Next
End Sub
--
Jacob (MVP - Excel)
"Kennedy" wrote:
> I have a spreadsheet that takes data from one worksheet and moves it to
> several worksheets, depending on a certain value. There are several
> worksheets where the data has been moved, based on a macro and formula I
> wrote.
> There are several rows that will always be blank, however a formula appears
> in those rows. Is there a way to create a macro that will look at all the
> "named" sheets individually and find any rows from 8-300 that have a formula
> in them, but do not contain actual data.
> For instance, worksheet NEO has formulas that feed off another sheet from
> row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
> but contain a formula. How can that e deleted.
> Same thing with worksheet OEN, except data is present in rows 8-100, with
> 101-300 being blank but containint formulas.
> Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:19:01 PM
Mike, All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if the formulas returns data back in rows 8-100, columns A-P, keep that, but delete all formulas from 101-300, columns A-P. The one thing consistent in all worksheets is that the data starts on row 8, and use columns A-P "Mike H" wrote: > Hi, > > Which columns are we looking at in these rows, all of them? > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Kennedy" wrote: > > > I have a spreadsheet that takes data from one worksheet and moves it to > > several worksheets, depending on a certain value. There are several > > worksheets where the data has been moved, based on a macro and formula I > > wrote. > > There are several rows that will always be blank, however a formula appears > > in those rows. Is there a way to create a macro that will look at all the > > "named" sheets individually and find any rows from 8-300 that have a formula > > in them, but do not contain actual data. > > For instance, worksheet NEO has formulas that feed off another sheet from > > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank > > but contain a formula. How can that e deleted. > > Same thing with worksheet OEN, except data is present in rows 8-100, with > > 101-300 being blank but containint formulas. > > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:29:01 PM
Jacob,
Maybe I did something wrong. Changed the "SheetNames" to "GEO", which is one
of the worksheets that I have. Tried running the macro and returned an error:
Run-time error '1004':
Method 'Range' of object'_Global' failed.
When looking at the macro, the For Each cell In Range("GEO") is
highlighted in yellow
"Jacob Skaria" wrote:
> You can try out the below macro. 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()>
>
> Namedrange "SheetNames"..Change to suit
>
> Sub DeleteRows()
> Dim ws As Worksheet, cell As Range, lngRow As Long
>
> For Each cell In Range("SheetNames")
> Set ws = Sheets(cell.Text)
>
> For lngRow = 24 To 8 Step -1
> If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
> Then ws.Rows(lngRow).Delete
> Next
> Next
> End Sub
>
>
>
> --
> Jacob (MVP - Excel)
>
>
> "Kennedy" wrote:
>
> > I have a spreadsheet that takes data from one worksheet and moves it to
> > several worksheets, depending on a certain value. There are several
> > worksheets where the data has been moved, based on a macro and formula I
> > wrote.
> > There are several rows that will always be blank, however a formula appears
> > in those rows. Is there a way to create a macro that will look at all the
> > "named" sheets individually and find any rows from 8-300 that have a formula
> > in them, but do not contain actual data.
> > For instance, worksheet NEO has formulas that feed off another sheet from
> > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
> > but contain a formula. How can that e deleted.
> > Same thing with worksheet OEN, except data is present in rows 8-100, with
> > 101-300 being blank but containint formulas.
> > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:37:01 PM
Try this,
Change the list of sheet names to the ones you want to work on
Sub marine()
Dim ws As Worksheet
Dim MyRange As Range
Dim CopyRange As Range
S = "Sheet1,Sheet2,Sheet9" 'Change to suit
V = Split(S, ",")
For Each ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(ws.Name, V, 0)) Then
Set MyRange = ws.Range("A8:A800")
For Each c In MyRange
If WorksheetFunction.Count(Range("A" & c.Row & ":" & "P" & c.Row)) = 0
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
End If
If Not CopyRange Is Nothing Then
CopyRange.Delete
Set CopyRange = Nothing
End If
Next
End Sub
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Kennedy" wrote:
> Mike,
> All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if
> the formulas returns data back in rows 8-100, columns A-P, keep that, but
> delete all formulas from 101-300, columns A-P.
> The one thing consistent in all worksheets is that the data starts on row 8,
> and use columns A-P
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Which columns are we looking at in these rows, all of them?
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Kennedy" wrote:
> >
> > > I have a spreadsheet that takes data from one worksheet and moves it to
> > > several worksheets, depending on a certain value. There are several
> > > worksheets where the data has been moved, based on a macro and formula I
> > > wrote.
> > > There are several rows that will always be blank, however a formula appears
> > > in those rows. Is there a way to create a macro that will look at all the
> > > "named" sheets individually and find any rows from 8-300 that have a formula
> > > in them, but do not contain actual data.
> > > For instance, worksheet NEO has formulas that feed off another sheet from
> > > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
> > > but contain a formula. How can that e deleted.
> > > Same thing with worksheet OEN, except data is present in rows 8-100, with
> > > 101-300 being blank but containint formulas.
> > > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:37:01 PM
Hi Kennedy
"Sheetnames" is a named range containing the sheet names you want to
delete.. If you are looking to delete just one sheet then try the below
version...
Sub DeleteRows()
Dim ws As Worksheet, lngRow As Long
Set ws = Sheets("GEO)
For lngRow = 24 To 8 Step -1
If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
Then ws.Rows(lngRow).Delete
Next
End Sub
--
Jacob (MVP - Excel)
"Kennedy" wrote:
> Jacob,
> Maybe I did something wrong. Changed the "SheetNames" to "GEO", which is one
> of the worksheets that I have. Tried running the macro and returned an error:
> Run-time error '1004':
> Method 'Range' of object'_Global' failed.
> When looking at the macro, the For Each cell In Range("GEO") is
> highlighted in yellow
>
> "Jacob Skaria" wrote:
>
> > You can try out the below macro. 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()>
> >
> > Namedrange "SheetNames"..Change to suit
> >
> > Sub DeleteRows()
> > Dim ws As Worksheet, cell As Range, lngRow As Long
> >
> > For Each cell In Range("SheetNames")
> > Set ws = Sheets(cell.Text)
> >
> > For lngRow = 24 To 8 Step -1
> > If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _
> > Then ws.Rows(lngRow).Delete
> > Next
> > Next
> > End Sub
> >
> >
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "Kennedy" wrote:
> >
> > > I have a spreadsheet that takes data from one worksheet and moves it to
> > > several worksheets, depending on a certain value. There are several
> > > worksheets where the data has been moved, based on a macro and formula I
> > > wrote.
> > > There are several rows that will always be blank, however a formula appears
> > > in those rows. Is there a way to create a macro that will look at all the
> > > "named" sheets individually and find any rows from 8-300 that have a formula
> > > in them, but do not contain actual data.
> > > For instance, worksheet NEO has formulas that feed off another sheet from
> > > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
> > > but contain a formula. How can that e deleted.
> > > Same thing with worksheet OEN, except data is present in rows 8-100, with
> > > 101-300 being blank but containint formulas.
> > > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:41:01 PM
oops,,
There's a bug in that, try this instead
Sub marine()
Dim ws As Worksheet
Dim MyRange As Range
Dim CopyRange As Range
S = "Sheet1,Sheet2,Sheet9" 'Change to suit
V = Split(S, ",")
For Each ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(ws.Name, V, 0)) Then
Set MyRange = ws.Range("A8:A800" & lastrow)
For Each c In MyRange
If WorksheetFunction.Count(ws.Range("A" & c.Row & ":" & "P" & c.Row)) =
0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
End If
If Not CopyRange Is Nothing Then
CopyRange.Delete
Set CopyRange = Nothing
End If
Next
End Sub
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Mike H" wrote:
> Try this,
>
> Change the list of sheet names to the ones you want to work on
>
> Sub marine()
> Dim ws As Worksheet
> Dim MyRange As Range
> Dim CopyRange As Range
> S = "Sheet1,Sheet2,Sheet9" 'Change to suit
> V = Split(S, ",")
> For Each ws In ThisWorkbook.Worksheets
> If Not IsError(Application.Match(ws.Name, V, 0)) Then
> Set MyRange = ws.Range("A8:A800")
> For Each c In MyRange
> If WorksheetFunction.Count(Range("A" & c.Row & ":" & "P" & c.Row)) = 0
> Then
> If CopyRange Is Nothing Then
> Set CopyRange = c.EntireRow
> Else
> Set CopyRange = Union(CopyRange, c.EntireRow)
> End If
> End If
> Next
> End If
> If Not CopyRange Is Nothing Then
> CopyRange.Delete
> Set CopyRange = Nothing
> End If
> Next
> End Sub
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Kennedy" wrote:
>
> > Mike,
> > All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if
> > the formulas returns data back in rows 8-100, columns A-P, keep that, but
> > delete all formulas from 101-300, columns A-P.
> > The one thing consistent in all worksheets is that the data starts on row 8,
> > and use columns A-P
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > Which columns are we looking at in these rows, all of them?
> > > --
> > > Mike
> > >
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > introduces the fewest assumptions while still sufficiently answering the
> > > question.
> > >
> > >
> > > "Kennedy" wrote:
> > >
> > > > I have a spreadsheet that takes data from one worksheet and moves it to
> > > > several worksheets, depending on a certain value. There are several
> > > > worksheets where the data has been moved, based on a macro and formula I
> > > > wrote.
> > > > There are several rows that will always be blank, however a formula appears
> > > > in those rows. Is there a way to create a macro that will look at all the
> > > > "named" sheets individually and find any rows from 8-300 that have a formula
> > > > in them, but do not contain actual data.
> > > > For instance, worksheet NEO has formulas that feed off another sheet from
> > > > row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank
> > > > but contain a formula. How can that e deleted.
> > > > Same thing with worksheet OEN, except data is present in rows 8-100, with
> > > > 101-300 being blank but containint formulas.
> > > > Same with other worksheets.
|
|
0 |
|
Utf
4/30/2010 3:44:01 PM
Similar Artilces:
Deleted batch
Hi guys: Is there any way I can determine who deleted a batch in GP? I have the batch number and I was looking in SY00500 but it appears that a deleted batch is removed from that table. TIA. Norman -- Norman This was posted in error please ignore. -- Norman "Norman Daley" wrote: > Hi guys: > > Is there any way I can determine who deleted a batch in GP? > > I have the batch number and I was looking in SY00500 but it appears that a > deleted batch is removed from that table. > > TIA. > > Norman > > ...
Formula #27
I am created a table containing weekly total of overtime for a number of employees (input table tab). I now want to create a chart (weekly overtime tab) for the weekly overtime. I want to be able to enter a date for a specific week and only create that week's chart. Is there a function that can lookup a date in the input table and use the numbers in the below rows to create the chart? Any information is appreciated. Thanks in advance Yes, you could do this, but you need to supply a bit more detail. What does your overtime sheet look like? A list of names in column A and overtime in ...
Exchange 2003 Server
I accidently deleted a Exchange server from a System Policy and it removed the server from the organization. How do I get that back????? Do I need to reinstall Exchange? You could do an authoritative restore of Active Directory otherwise you will need to do another install for the server. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright....
Deleted e-mails
I have outlook 2000. Is their any way to retrieve deleted e-mails (deleted from the deleted items file)? If you connect to an Exchange server configured to allow this, yes. If not, no, not unless you had a backup of your PST file...sorry. Senait wrote: > I have outlook 2000. Is their any way to retrieve deleted > e-mails (deleted from the deleted items file)? ...
Can I delete my Entourage 2004 Identities once I've upgraded to 20
I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I didn't remove Office 2004. Now I've got two identical (and huge) Identity folders, and I was wondering if I can delete the 2004 Identity if I'm going to use Entourage 2008? Thanks! On 4/19/10 9:26 AM, Jg wrote: > I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I > didn't remove Office 2004. Now I've got two identical (and huge) Identity > folders, and I was wondering if I can delete the 2004 Identity if I'm going > to use Entourage 2008?...
How do I prevent users to delete a worksheet, but allow to edit?
How can I allow other users to update an workbook, but not to delete any tabs or existing information? Take a look at protect worksheet and protect workbook these both can be found in tools>protection. Use help to guide you the rest of the way. James "nb91386@yahoo.com" wrote: > How can I allow other users to update an workbook, but not to delete any tabs > or existing information? Protect the workbook. Tools|protection|Protect workbook Just leave Structure checked. (Users won't be able to rename or change the order of the worksheets, either.) But be aware that...
Deleting info in a column
I am exporting info from MS Project and my dates come out formatted as such: 2/5/2007 8:00 I do not need to see the time, and i know there is a function (or something) that will run through the column and delete the time to just show 2/5/2007 ...... I can not remember how to do this. any help appreciated! If you just want to change the way the info is displayed, one way would be to format the column as a date. In article <1179770254.963135.161610@a26g2000pre.googlegroups.com>, JPD <jdescant@gmail.com> wrote: > I am exporting info from MS Project and my dates come out...
Trend Formula
What does the symbol E stand for in excel graphical trend formula? Is it 10, or exponential e? When I try to test the formulae I get the correct x=0 point but others are wrong; either way. I am using an x raised to the power 5 polynomial A number such as 1.234E-3 is to be read as 1.234�10^(-3) or 0.001234 Copying the trendline equation values can give terrible results unless you first format the equation for maximum precision Better yet use LINEST to but values into cells that you can reference to generate the forecast values See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm ...
Moving Rows to Columns
I have a text file that I am importing that has information on multiple rows (each entry has three rows of data). I would move each row of data into a column. Anyone have any ideas/macros on how to do this? Dear Paul Select the row of data, Copy it, move to the cell of your choice (I would recommend on a new worksheet).Edit/Paste Special/Tick the transpose box. Hope this helps Paul Falla >-----Original Message----- >I have a text file that I am importing that has >information on multiple rows (each entry has three rows of >data). I would move each row of data into a co...
deleting specific emails from exchange server
Is it possible to delete specific emails from exchange server 2000? I have sceneraio where 20,000 have been generated for a single email address and I want to be able to delete these before delivery to the end user account (outlook 2003). Cheers in advance Ollie Riches Check your mail queues, order them by Sender, bulk select and then delete with no NDR. Oliver "Ollie Riches" <ollie_riches@hotmail.com> wrote in message news:OSIRe2iNHHA.2232@TK2MSFTNGP02.phx.gbl... > Is it possible to delete specific emails from exchange server 2000? > > I have scenerai...
Button to delete all records of a table
Hello, Can anyone help me how can we make it just to pressed the button, then all records in a table will be deleted/ purge out. Background: I want to distrtibute copy of my church database to all pastors of our church. In my membership database, there is a child table of Notes...in which a church clerck can put a note for the member. Before I give the copy of the database to pastors, I want to purge out all the content of the table of Notes, so that later I can collect it from them and inport their notes table to be combined with the central database which is the one a sc...
Deleting GL accounts
We are receiving an error message when trying to delete some GL accounts - this account has a balance or YTD activity and cannot be deleted. The account has had no activity for several years and has been inactivated. We just processed our year end and a BBF of zero was brought forward. Do we need to Remove Account History before we can delete these accoutns? Yes, you need to delete history. To delete an account it must meet the following conditions - 1. No balance 2. No activity for an open period 3. No history amounts 4. Not part of an allocation account 5. Not part of an unpo...
formulas in excel #2
I'm having trouble dislaying the results of my formulas. I've typed the formulas in and that's what is displaying instead of the results. I know I'm overlooking something but I just can't seem to figure it out. Can someone please point me in the right direction. Thanks in advance. Tom Hi Tom your cell is porbably formated as 'Text'. Goto 'Format - cells' and change the format to 'General'. After that reenter your formula HTH Frank Tom wrote: > I'm having trouble dislaying the results of my formulas. I've typed > the formulas in...
Check Book Formula
I'm a real green horn when it comes to excel, I've searched the data base and am pretty baffled by some of the questions...Wow....anyways, I feel kind of silly asking as this is probably very simple. I just don't know how i would go about setting up a check book style spreadsheet. The last three colums would be Income, Expense, Balance. I understand how to add or subtract, what I don't know is how to I tell the spreadsheet to SUBTRACT the expense to get the Balance or and ADD the income to get the balance in each row. Do I have to type a formula in each row to do ...
Loan
Hi, I have the following criteria: Outstanding principal: 616, 000/= Interest Rate: 13.25% Remaining tenure: 22 months Monthly Payment: 31, 672/= Lets say I increase my monthly payment by 2,000/= then what would the benefit (savings in interest and decrease in payment cycle). I know there are some web based tools that will allow me to calculate this. I'm looking for a formula to perform the above calculation. Any help would be appreciated. Thanks! On Dec 15, 10:55 pm, D2 <dhap...@yahoo.com> wrote: > I have the following criteria: > Outstanding principal: 616, 000 > Inte...
Hide formulas but not lock cells or contents
I've created a worksheet which changes depending on the entry in the Data Validation list but I like to hide all the formulas in this worksheet. I've tried selecting hidden in the Format/Protection and then selecting lock cells Protect worksheet in Tools/Protection but then this does not allow to select the drop down menu in the Data Validation box. I dont want to lock cells as they change with dynamically with data validation input PLUS I use Auto Filters but how can I still hide the formulas. Any suggestions Thx You COULD use Application.DisplayFormulaBar = False but the user c...
Can't insert Rows?
When I try and insert a row I get a prompt saying "To prevent possible loss of data, Excel cannot shift non-blank cells off the worksheet. Try to locate the last non-blank cell by pressing CTRL+END and delete or clear all cells in between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used." Can anyone advise (a) Why this happens and (b) How to stop it happening as I've tried doing what the prompt says and can't seem to solve it...and I really need to insert a row, rather than cut & paste. Any help great...
test for a formula
Will someone help me write a macro to check if a selected cell contains a formula. Please Hi Lashio, Try something like: '=================>> Public Sub FormulaTest() If ActiveCell.HasFormula Then 'Do something, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is a formula cell" Else 'do something else, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is not a formula cell" ...
Mails are automaticaly deleted after being reading
Hello all, I've got a little problem with Outlook 2002. Wenn I get a new message in the receive box and I open it to read it, and wenn I click on the cross to close the email, the mail is automaticaly deleted from outlook without being moving in the deleted items folder. The problem is if I don't answer directly, the message is lost. I already checked the options but I can't found which one musts be changed to keep all the messages in the incomming box. I hoppe you will anderstand my problem and that someone will be able to help me Best regards, Tommy. Tommy <thierry...
How do I stop Excel from automatically resizing my row height?
I have a spreadsheet with approximately 3800 rows and about 40 columns of data that I'm constantly filtering and massaging data in. It's getting frustrating to have my rows all of a sudden jump to a height of about 1/4 of the screen when I shrink some column widths to fit more columns on the screen. (Freeze panes won't allow me to split the screen also.) How do I disable the autofit row height? ...
Previously permitted folders will not delete
Using Outlook 2000, I have found that when User A has been given permission to access User B's folder(s), then User B removes the permissions for User A to access the folder(s) any longer, User A's Outlook setup will still show User B's folder(s) in their list of accessible folders when in option File => Open => "folder". Is there a way to delete the no longer accessible folder from the list? Thx. Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;292024&Product=out -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professiona...
Lock Rows
Hi, I have a worksheet where the first 5 rows (A1:A5) hold a number of headings which I want to be locked. ie when the user fills the current screen with data and wishes to scroll down using the vertical scroll bar, the actual headings don't dissapear from the screen. Easy enough, but can't figure out how. Thanks Anthony Go into A6 and then go to window/freeze panes "Anthony" wrote: > Hi, > I have a worksheet where the first 5 rows (A1:A5) hold a number of headings > which I want to be locked. ie when the user fills the current screen with > data and wish...
Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...
Ignore Blank
Hi to all, I have a range name that might have blank cells. I used it in a "validating data" settings. I hade a sourse=rangeName and also have chose the "list" option. The "Ignore Blank" is selected. When I use this list I can see the Blank cells!! Why this option is not working ???? You misunderstand the purpose of "ignore blanks" Blank Cells in Source List If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this: Select the cell that contains a d...
Deleting worksheets
What is the best way to erase a worksheet, it shouldn't leave any trace - whats so ever - on the hard drive Hi see your other post -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > What is the best way to erase a worksheet, it shouldn't > leave any trace - whats so ever - on the hard drive What ? >-----Original Message----- >Hi >see your other post > >-- >Regards >Frank Kabel >Frankfurt, Germany > > >anonymous@discussions.microsoft.com wrote: >> What is the best way to erase a worksheet, it should...
Hi guys: Is there any way I can determine who deleted a batch in GP? I have the batch number and I was looking in SY00500 but it appears that a deleted batch is removed from that table. TIA. Norman -- Norman This was posted in error please ignore. -- Norman "Norman Daley" wrote: > Hi guys: > > Is there any way I can determine who deleted a batch in GP? > > I have the batch number and I was looking in SY00500 but it appears that a > deleted batch is removed from that table. > > TIA. > > Norman > > ...
Formula #27
I am created a table containing weekly total of overtime for a number of employees (input table tab). I now want to create a chart (weekly overtime tab) for the weekly overtime. I want to be able to enter a date for a specific week and only create that week's chart. Is there a function that can lookup a date in the input table and use the numbers in the below rows to create the chart? Any information is appreciated. Thanks in advance Yes, you could do this, but you need to supply a bit more detail. What does your overtime sheet look like? A list of names in column A and overtime in ...
Exchange 2003 Server
I accidently deleted a Exchange server from a System Policy and it removed the server from the organization. How do I get that back????? Do I need to reinstall Exchange? You could do an authoritative restore of Active Directory otherwise you will need to do another install for the server. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright....
Deleted e-mails
I have outlook 2000. Is their any way to retrieve deleted e-mails (deleted from the deleted items file)? If you connect to an Exchange server configured to allow this, yes. If not, no, not unless you had a backup of your PST file...sorry. Senait wrote: > I have outlook 2000. Is their any way to retrieve deleted > e-mails (deleted from the deleted items file)? ...
Can I delete my Entourage 2004 Identities once I've upgraded to 20
I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I didn't remove Office 2004. Now I've got two identical (and huge) Identity folders, and I was wondering if I can delete the 2004 Identity if I'm going to use Entourage 2008? Thanks! On 4/19/10 9:26 AM, Jg wrote: > I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I > didn't remove Office 2004. Now I've got two identical (and huge) Identity > folders, and I was wondering if I can delete the 2004 Identity if I'm going > to use Entourage 2008?...
How do I prevent users to delete a worksheet, but allow to edit?
How can I allow other users to update an workbook, but not to delete any tabs or existing information? Take a look at protect worksheet and protect workbook these both can be found in tools>protection. Use help to guide you the rest of the way. James "nb91386@yahoo.com" wrote: > How can I allow other users to update an workbook, but not to delete any tabs > or existing information? Protect the workbook. Tools|protection|Protect workbook Just leave Structure checked. (Users won't be able to rename or change the order of the worksheets, either.) But be aware that...
Deleting info in a column
I am exporting info from MS Project and my dates come out formatted as such: 2/5/2007 8:00 I do not need to see the time, and i know there is a function (or something) that will run through the column and delete the time to just show 2/5/2007 ...... I can not remember how to do this. any help appreciated! If you just want to change the way the info is displayed, one way would be to format the column as a date. In article <1179770254.963135.161610@a26g2000pre.googlegroups.com>, JPD <jdescant@gmail.com> wrote: > I am exporting info from MS Project and my dates come out...
Trend Formula
What does the symbol E stand for in excel graphical trend formula? Is it 10, or exponential e? When I try to test the formulae I get the correct x=0 point but others are wrong; either way. I am using an x raised to the power 5 polynomial A number such as 1.234E-3 is to be read as 1.234�10^(-3) or 0.001234 Copying the trendline equation values can give terrible results unless you first format the equation for maximum precision Better yet use LINEST to but values into cells that you can reference to generate the forecast values See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm ...
Moving Rows to Columns
I have a text file that I am importing that has information on multiple rows (each entry has three rows of data). I would move each row of data into a column. Anyone have any ideas/macros on how to do this? Dear Paul Select the row of data, Copy it, move to the cell of your choice (I would recommend on a new worksheet).Edit/Paste Special/Tick the transpose box. Hope this helps Paul Falla >-----Original Message----- >I have a text file that I am importing that has >information on multiple rows (each entry has three rows of >data). I would move each row of data into a co...
deleting specific emails from exchange server
Is it possible to delete specific emails from exchange server 2000? I have sceneraio where 20,000 have been generated for a single email address and I want to be able to delete these before delivery to the end user account (outlook 2003). Cheers in advance Ollie Riches Check your mail queues, order them by Sender, bulk select and then delete with no NDR. Oliver "Ollie Riches" <ollie_riches@hotmail.com> wrote in message news:OSIRe2iNHHA.2232@TK2MSFTNGP02.phx.gbl... > Is it possible to delete specific emails from exchange server 2000? > > I have scenerai...
Button to delete all records of a table
Hello, Can anyone help me how can we make it just to pressed the button, then all records in a table will be deleted/ purge out. Background: I want to distrtibute copy of my church database to all pastors of our church. In my membership database, there is a child table of Notes...in which a church clerck can put a note for the member. Before I give the copy of the database to pastors, I want to purge out all the content of the table of Notes, so that later I can collect it from them and inport their notes table to be combined with the central database which is the one a sc...
Deleting GL accounts
We are receiving an error message when trying to delete some GL accounts - this account has a balance or YTD activity and cannot be deleted. The account has had no activity for several years and has been inactivated. We just processed our year end and a BBF of zero was brought forward. Do we need to Remove Account History before we can delete these accoutns? Yes, you need to delete history. To delete an account it must meet the following conditions - 1. No balance 2. No activity for an open period 3. No history amounts 4. Not part of an allocation account 5. Not part of an unpo...
formulas in excel #2
I'm having trouble dislaying the results of my formulas. I've typed the formulas in and that's what is displaying instead of the results. I know I'm overlooking something but I just can't seem to figure it out. Can someone please point me in the right direction. Thanks in advance. Tom Hi Tom your cell is porbably formated as 'Text'. Goto 'Format - cells' and change the format to 'General'. After that reenter your formula HTH Frank Tom wrote: > I'm having trouble dislaying the results of my formulas. I've typed > the formulas in...
Check Book Formula
I'm a real green horn when it comes to excel, I've searched the data base and am pretty baffled by some of the questions...Wow....anyways, I feel kind of silly asking as this is probably very simple. I just don't know how i would go about setting up a check book style spreadsheet. The last three colums would be Income, Expense, Balance. I understand how to add or subtract, what I don't know is how to I tell the spreadsheet to SUBTRACT the expense to get the Balance or and ADD the income to get the balance in each row. Do I have to type a formula in each row to do ...
Loan
Hi, I have the following criteria: Outstanding principal: 616, 000/= Interest Rate: 13.25% Remaining tenure: 22 months Monthly Payment: 31, 672/= Lets say I increase my monthly payment by 2,000/= then what would the benefit (savings in interest and decrease in payment cycle). I know there are some web based tools that will allow me to calculate this. I'm looking for a formula to perform the above calculation. Any help would be appreciated. Thanks! On Dec 15, 10:55 pm, D2 <dhap...@yahoo.com> wrote: > I have the following criteria: > Outstanding principal: 616, 000 > Inte...
Hide formulas but not lock cells or contents
I've created a worksheet which changes depending on the entry in the Data Validation list but I like to hide all the formulas in this worksheet. I've tried selecting hidden in the Format/Protection and then selecting lock cells Protect worksheet in Tools/Protection but then this does not allow to select the drop down menu in the Data Validation box. I dont want to lock cells as they change with dynamically with data validation input PLUS I use Auto Filters but how can I still hide the formulas. Any suggestions Thx You COULD use Application.DisplayFormulaBar = False but the user c...
Can't insert Rows?
When I try and insert a row I get a prompt saying "To prevent possible loss of data, Excel cannot shift non-blank cells off the worksheet. Try to locate the last non-blank cell by pressing CTRL+END and delete or clear all cells in between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used." Can anyone advise (a) Why this happens and (b) How to stop it happening as I've tried doing what the prompt says and can't seem to solve it...and I really need to insert a row, rather than cut & paste. Any help great...
test for a formula
Will someone help me write a macro to check if a selected cell contains a formula. Please Hi Lashio, Try something like: '=================>> Public Sub FormulaTest() If ActiveCell.HasFormula Then 'Do something, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is a formula cell" Else 'do something else, e.g.: MsgBox "Cell " & ActiveCell.Address(0, 0) _ & " is not a formula cell" ...
Mails are automaticaly deleted after being reading
Hello all, I've got a little problem with Outlook 2002. Wenn I get a new message in the receive box and I open it to read it, and wenn I click on the cross to close the email, the mail is automaticaly deleted from outlook without being moving in the deleted items folder. The problem is if I don't answer directly, the message is lost. I already checked the options but I can't found which one musts be changed to keep all the messages in the incomming box. I hoppe you will anderstand my problem and that someone will be able to help me Best regards, Tommy. Tommy <thierry...
How do I stop Excel from automatically resizing my row height?
I have a spreadsheet with approximately 3800 rows and about 40 columns of data that I'm constantly filtering and massaging data in. It's getting frustrating to have my rows all of a sudden jump to a height of about 1/4 of the screen when I shrink some column widths to fit more columns on the screen. (Freeze panes won't allow me to split the screen also.) How do I disable the autofit row height? ...
Previously permitted folders will not delete
Using Outlook 2000, I have found that when User A has been given permission to access User B's folder(s), then User B removes the permissions for User A to access the folder(s) any longer, User A's Outlook setup will still show User B's folder(s) in their list of accessible folders when in option File => Open => "folder". Is there a way to delete the no longer accessible folder from the list? Thx. Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;292024&Product=out -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professiona...
Lock Rows
Hi, I have a worksheet where the first 5 rows (A1:A5) hold a number of headings which I want to be locked. ie when the user fills the current screen with data and wishes to scroll down using the vertical scroll bar, the actual headings don't dissapear from the screen. Easy enough, but can't figure out how. Thanks Anthony Go into A6 and then go to window/freeze panes "Anthony" wrote: > Hi, > I have a worksheet where the first 5 rows (A1:A5) hold a number of headings > which I want to be locked. ie when the user fills the current screen with > data and wish...
Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...
Ignore Blank
Hi to all, I have a range name that might have blank cells. I used it in a "validating data" settings. I hade a sourse=rangeName and also have chose the "list" option. The "Ignore Blank" is selected. When I use this list I can see the Blank cells!! Why this option is not working ???? You misunderstand the purpose of "ignore blanks" Blank Cells in Source List If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this: Select the cell that contains a d...
Deleting worksheets
What is the best way to erase a worksheet, it shouldn't leave any trace - whats so ever - on the hard drive Hi see your other post -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > What is the best way to erase a worksheet, it shouldn't > leave any trace - whats so ever - on the hard drive What ? >-----Original Message----- >Hi >see your other post > >-- >Regards >Frank Kabel >Frankfurt, Germany > > >anonymous@discussions.microsoft.com wrote: >> What is the best way to erase a worksheet, it should...