Deleting Blank lines with VBA

Help I have a macro that runs multiple formulas.  However, when the formulas 
are
finished I am left with about 64000 blank lines.  I am unable to insert a 
row on the 
worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank 
Cell
using CTRL-END).

Here is a same of some of the formulas  I am running ( the macro is very 
large).

ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27"
ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28"
ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29"
ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30"
ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31"
ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32"
ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33"
ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34"
ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35"
ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36"
ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37"

'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO 
BOTTOM OF SHEET
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = 
"=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
    'Defines a variable called anchor cell
    Application.Goto Reference:="TrunkFormulaI", Scroll:=False
    Selection.Copy
    AnchorCell = ActiveCell.Offset(1, 0).Address
    ActiveCell.Offset(0, -1).Select
    ActiveCell.End(xlDown).Select
    EndCell = ActiveCell.Offset(0, 1).Address
    Range(AnchorCell, EndCell).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.Goto Reference:="TrunkFormulaI", Scroll:=False
    Range("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    

'RUN-TIME ERROR 1004 Appears HERE

    Range("A1").EntireRow.Insert


Any help would be appreciated.

0
VexedFist (5)
4/5/2007 12:24:00 PM
excel 39879 articles. 2 followers. Follow

4 Replies
838 Views

Similar Articles

[PageSpeed] 32

Can you pick out a column that is always used when that row is used?  If you can
then maybe you can just fill the formulas through that last row.

Dim LastRow as long
with activesheet
   lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
   with .range("AA1:aa" & lastrow)
      .formular1c1 _
         "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
      .value = .value
   end with
end with




VexedFist wrote:
> 
> Help I have a macro that runs multiple formulas.  However, when the formulas
> are
> finished I am left with about 64000 blank lines.  I am unable to insert a
> row on the
> worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank
> Cell
> using CTRL-END).
> 
> Here is a same of some of the formulas  I am running ( the macro is very
> large).
> 
> ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36"
> ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37"
> 
> 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO
> BOTTOM OF SHEET
>     Range("AA1").Select
>     ActiveCell.FormulaR1C1 =
> "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
>     'Defines a variable called anchor cell
>     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
>     Selection.Copy
>     AnchorCell = ActiveCell.Offset(1, 0).Address
>     ActiveCell.Offset(0, -1).Select
>     ActiveCell.End(xlDown).Select
>     EndCell = ActiveCell.Offset(0, 1).Address
>     Range(AnchorCell, EndCell).Select
>     ActiveSheet.Paste
>     Application.CutCopyMode = False
>     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
>     Range("G:G").Select
>     Selection.Copy
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>         False, Transpose:=False
>     Application.CutCopyMode = False
> 
> 
> 'RUN-TIME ERROR 1004 Appears HERE
> 
>     Range("A1").EntireRow.Insert
> 
> Any help would be appreciated.

-- 

Dave Peterson
0
petersod (12004)
4/5/2007 12:35:12 PM
Dave,

When I run this I get:

Run-Time Error 438

Object doens't support this Property or Method??



"Dave Peterson" wrote:

> Can you pick out a column that is always used when that row is used?  If you can
> then maybe you can just fill the formulas through that last row.
> 
> Dim LastRow as long
> with activesheet
>    lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
>    with .range("AA1:aa" & lastrow)
>       .formular1c1 _
>          "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
>       .value = .value
>    end with
> end with
> 
> 
> 
> 
> VexedFist wrote:
> > 
> > Help I have a macro that runs multiple formulas.  However, when the formulas
> > are
> > finished I am left with about 64000 blank lines.  I am unable to insert a
> > row on the
> > worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank
> > Cell
> > using CTRL-END).
> > 
> > Here is a same of some of the formulas  I am running ( the macro is very
> > large).
> > 
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37"
> > 
> > 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO
> > BOTTOM OF SHEET
> >     Range("AA1").Select
> >     ActiveCell.FormulaR1C1 =
> > "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
> >     'Defines a variable called anchor cell
> >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> >     Selection.Copy
> >     AnchorCell = ActiveCell.Offset(1, 0).Address
> >     ActiveCell.Offset(0, -1).Select
> >     ActiveCell.End(xlDown).Select
> >     EndCell = ActiveCell.Offset(0, 1).Address
> >     Range(AnchorCell, EndCell).Select
> >     ActiveSheet.Paste
> >     Application.CutCopyMode = False
> >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> >     Range("G:G").Select
> >     Selection.Copy
> >     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> >         False, Transpose:=False
> >     Application.CutCopyMode = False
> > 
> > 
> > 'RUN-TIME ERROR 1004 Appears HERE
> > 
> >     Range("A1").EntireRow.Insert
> > 
> > Any help would be appreciated.
> 
> -- 
> 
> Dave Peterson
> 
0
VexedFist (5)
4/5/2007 3:06:07 PM
Dave,

When I run this i get:

Run-Time Error 438

Object doesn't support this Property or Method!!



"Dave Peterson" wrote:

> Can you pick out a column that is always used when that row is used?  If you can
> then maybe you can just fill the formulas through that last row.
> 
> Dim LastRow as long
> with activesheet
>    lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
>    with .range("AA1:aa" & lastrow)
>       .formular1c1 _
>          "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
>       .value = .value
>    end with
> end with
> 
> 
> 
> 
> VexedFist wrote:
> > 
> > Help I have a macro that runs multiple formulas.  However, when the formulas
> > are
> > finished I am left with about 64000 blank lines.  I am unable to insert a
> > row on the
> > worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank
> > Cell
> > using CTRL-END).
> > 
> > Here is a same of some of the formulas  I am running ( the macro is very
> > large).
> > 
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36"
> > ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37"
> > 
> > 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO
> > BOTTOM OF SHEET
> >     Range("AA1").Select
> >     ActiveCell.FormulaR1C1 =
> > "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
> >     'Defines a variable called anchor cell
> >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> >     Selection.Copy
> >     AnchorCell = ActiveCell.Offset(1, 0).Address
> >     ActiveCell.Offset(0, -1).Select
> >     ActiveCell.End(xlDown).Select
> >     EndCell = ActiveCell.Offset(0, 1).Address
> >     Range(AnchorCell, EndCell).Select
> >     ActiveSheet.Paste
> >     Application.CutCopyMode = False
> >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> >     Range("G:G").Select
> >     Selection.Copy
> >     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> >         False, Transpose:=False
> >     Application.CutCopyMode = False
> > 
> > 
> > 'RUN-TIME ERROR 1004 Appears HERE
> > 
> >     Range("A1").EntireRow.Insert
> > 
> > Any help would be appreciated.
> 
> -- 
> 
> Dave Peterson
> 
0
VexedFist (5)
4/5/2007 3:08:03 PM
There was a typo.  I dropped an equal sign:

      .formular1c1 _
        = "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"



VexedFist wrote:
> 
> Dave,
> 
> When I run this i get:
> 
> Run-Time Error 438
> 
> Object doesn't support this Property or Method!!
> 
> "Dave Peterson" wrote:
> 
> > Can you pick out a column that is always used when that row is used?  If you can
> > then maybe you can just fill the formulas through that last row.
> >
> > Dim LastRow as long
> > with activesheet
> >    lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
> >    with .range("AA1:aa" & lastrow)
> >       .formular1c1 _
> >          "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
> >       .value = .value
> >    end with
> > end with
> >
> >
> >
> >
> > VexedFist wrote:
> > >
> > > Help I have a macro that runs multiple formulas.  However, when the formulas
> > > are
> > > finished I am left with about 64000 blank lines.  I am unable to insert a
> > > row on the
> > > worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank
> > > Cell
> > > using CTRL-END).
> > >
> > > Here is a same of some of the formulas  I am running ( the macro is very
> > > large).
> > >
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36"
> > > ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37"
> > >
> > > 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO
> > > BOTTOM OF SHEET
> > >     Range("AA1").Select
> > >     ActiveCell.FormulaR1C1 =
> > > "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
> > >     'Defines a variable called anchor cell
> > >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> > >     Selection.Copy
> > >     AnchorCell = ActiveCell.Offset(1, 0).Address
> > >     ActiveCell.Offset(0, -1).Select
> > >     ActiveCell.End(xlDown).Select
> > >     EndCell = ActiveCell.Offset(0, 1).Address
> > >     Range(AnchorCell, EndCell).Select
> > >     ActiveSheet.Paste
> > >     Application.CutCopyMode = False
> > >     Application.Goto Reference:="TrunkFormulaI", Scroll:=False
> > >     Range("G:G").Select
> > >     Selection.Copy
> > >     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> > >         False, Transpose:=False
> > >     Application.CutCopyMode = False
> > >
> > >
> > > 'RUN-TIME ERROR 1004 Appears HERE
> > >
> > >     Range("A1").EntireRow.Insert
> > >
> > > Any help would be appreciated.
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
4/5/2007 5:49:42 PM
Reply:

Similar Artilces:

Bug with Microsoft Publisher and VBA (it seems)
Hi, first i want to apologize for my porr english, it s not really my natural langage even if i have to use it every day in my office (poor listener :)) I am an engeener in a software company and use microsoft .net regulary. Recently i have to reinstall my systeme (XP Pro SP2 with all window update patch) and resinstall the .Net Architect 2003. Then the Office 2003 with publisher (I use it a lot). But after the reinstall the soft has been stop running. The symptoms are the following : - when i open a pub file, the application close immediatly - when i open publisher, i can start a new project ...

Inserting A Blank Line, Again
Below is an example of text data imported into Excel. Would it be possible to insert a blank line between the different races times as displayed below? Again, the data varies. The data is imported into A2. There is other data in columns B & C. I am able to sort the times with the help of Pete (UK). 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 1:40Newton Abbot 2:05Kempton 2:05Kempton 2:05Kempton 2:05Kempton 2:05Kempton 2:05Kempton 2:05Kempton 2:05Kempton 2:10Newton Abbot 2:10Ne...

Extra lines inserting in replies/forwards
Hi When I reply or forward Outlook messages, I'm getting about 20 lines (blank) put in between the replied/forwarded message "Subject" line and the text. Can anyone help me to stop this? Thanks. David "DG" <dgee@papercontracts.co.uk> wrote in message news:OGJgUTPoFHA.3552@TK2MSFTNGP10.phx.gbl... > Hi > When I reply or forward Outlook messages, I'm getting about 20 lines > (blank) put in between the replied/forwarded message "Subject" line and > the text. Can anyone help me to stop this? Thanks. David > Is it set to reply...

Line splits across 2 pages
In one of my access reports I have there is one line that splits across to pages where the top of the letters appear on page 1 and the bottom of the letters appear on page 2 Any idea why it happens and how to avoid it? Thank you, Samuel Samuel wrote: > In one of my access reports I have there is one line that splits > across to pages where the top of the letters appear on page 1 and the > bottom of the letters appear on page 2 > > Any idea why it happens and how to avoid it? > > Thank you, > Samuel Go into design mode and shorten that line. -- Joseph Mee...

Combined bar/Line graph defaults to bar
In Excel 2007 I have a Combined bar/Line graph get it's data from a pivottable. Intermittently, when I change the pivottable filters, the combined bar/Line graph defaults to just a bar graph. I have tried 2 approaches with the same results - 1. Have the graph select each set of data from a range 2. Have the graph point generically at the pivottable and dynamically adjust itself -- Thank You in Advance, Michael Anderson Pivot charts are notorious for resetting their formatting when the table is refreshed or re-pivoted. What you have to do is change each series: right click on th...

Clean-up queue with deleted activities
Hi, Yesterday I created campaign telephone call activities and assigned them to a queue. I decided to delete the activities and now I have the problem that they are still shown in the queue. When I try to delete them there I get the error record not found. Also I cannot remove the queue. Please help me to cleanup this queue. Thank you, Regards Jan instead of selecting all , choos for example 10 and then click on Delete or choose just one and delete and see if you still get the erro? ------ Aamir Blog = http://mscrmsupport.wordpress.com/ No, same error. "Aamir Bashir" wro...

Deleting an outbox email without opening Outlook.
Hi, I'm using Outlook 2002. There is an email that seems to be stuck in my outbox and has been sent 54 times to the recipient! If I try to delete the email from the outbox I get an error message saying that it is unable to delete and please restart outlook to rectify the problem. This doesn't help at all. Once Outlook is restarted the sending process will start all over again. Is there a way that I can delete the email without opening Outlook so that when I start the program next time there is nothing in the outbox. Thanks in advance for any help. Regards, Sonars UK. ...

Execute a variable as an insertion into a VBA code line
2003 & 2007 Is there a way to obtain, via Inputbox, to run the code line below: "MyToolsObject.Test" X = "MyToolsObject." & Application.InputBox("VB.NET File to test: ", "ENTER PROCEDURE NAME") Assume: X = "MyToolsObject.Test " Effectively, how can I "Execute" X as if it were the codeline below? Sub DecodeRunDLL() ' Dim MyToolsObject As ToolsNET.Tools Set MyToolsObject = New ToolsNET.Tools MyToolsObject.Test ' <<<<< *********** ...

Cannot delet/view mailbox
Running Exchange 5.5 sp4 on a 2000 Server In "Mailbox Resources" I have a mailbox named "Admin" without any other information other than size and number of items for this mailbox. Also,it does not show in the "Recipients" directory. When I try to view the properties for the mailbox I receive an error message saying: "The object cannot be found in the directory. This may be because replication has not completed Microsoft exchange directory ID no:c1010aae". All other features on the exchange server work fine, but my backup fails because of this mailb...

how to print a blank invoice in money 2004?
ok there has got to be a way to print a blank invoice in money 2004 right? the program would be useless to most people if you can't print a blank one for the service call away from your desk so please if you have done this show me how I,m new at this thanks jim I don't think you can without first designating a customer. And then, MSMo won't let you print an invoice without adding at least one item. I would suggest you greate a invoice in Excel, powerpoint or word to take with you -- or better yet stop in Staples a buy a bunch of blank invoices for a couple of bucks. &g...

Protect Worksheet but allow to insert or delete rows
I have set up a worksheet, locked and unlocked cells as needed and I want to allow the user to insert and delete rows. But, when I set protection and tick "Insert Rows" and tick "Delete Rows"; the menu only will allow "Insert Rows", the "Delete Rows" field is not accessible. Solution? Do you get an error message saying that you can't delete the row since there are locked cells on that row? I do when I protect a worksheet using your options and then rightclick on a row number and choose delete. (And that row contains locked cells.) Bob L Hill...

How do I resize a worksheet after deleting rows?
After deleting rows in a spreadsheet Excel seems to remember the blank rows, and will try to print them unless I reset the print area. Also, when I subtotal the grand totals appear after the blank rows. Is there a way to re-set the size? Save the workbook after deleting the Entire rows and before printing. -- Regards, Tom Ogilvy "Jay Jay" <JayJay@discussions.microsoft.com> wrote in message news:7A4B3295-C46C-4100-94B5-9AB4500DCFB2@microsoft.com... > After deleting rows in a spreadsheet Excel seems to remember the blank rows, > and will try to print them unless I...

Getting straight lines in a best fit trendline of semi log data?
Thanks in advance for any light you can shed. I have been asked to create a graph that looks like this: http://www.jci.org/cgi/content/full/98/2/490/F4 It is a semi-log representation of a time series. I have created a spreadsheet here: http://ccbc.unc.edu/documents/logarithmic_test_graph.xls As you can see, the trendlines are curved. I have serched the archives but I'm missing a basic understanding of what to do. For example, the TREND function just gave me a specific value. If anyone can tell me how to make my clunky graph resemble the spiify published one, you will save both my...

how to delete account in 'Accounts List'
I have 4 accounts in Accounts List section that I remove, but when I restart Money, they appear again. I've also taken them off my Favorites list but no luck. In microsoft.public.money, core000 wrote: >I have 4 accounts in Accounts List section that I remove, but when I >restart Money, they appear again. I've also taken them off my >Favorites list but no luck. You might want to Close the accounts by right-clicking in the AccountList. Then set the view to not show with SortAccountListBy->HideClosedAccounts. I suspect these are accounts that are/were at the same instit...

Combined bar and line chart (two Y axis) how to make excel know which data goes where
Hello all, after the super fast problem fixing last time i had a problem, i'd like to ask the excel collective for some more help... :) now trying to plot a combined bar chart and line graph. I basically want all the sets of data except one to appear as columns plotted against the axis on the left and the last set of data (which is a cumulative value of the data appearing for the columns) plotted against the right hand axis as a line. I can plot the graph fine, but out of the 7 sets of data which i want to appear as 6 sets of columns and one line, i get four sets of columns and three ...

2 VBA Q
1. Cells(i, 9) = DateDiff(Cells(i,7), "12/31/2003", "m") - gives me type mismatch error - I need to check that the end date is before 12/31/2003 and then calculate the number of months till the end of the year. 2. If (Cells(i, 7) = "Complete" & Sheets("Plan").Cells(i, 7) = "mm/dd/yyyy") - does not work. It works for - If (Cells(i, 7) = "Complete") However, I need to check - that there is a date in the cells - Sheets("Plan").cells(i,7) else I want to leave the original comment - "Complete". TIA. di...

POP Line Item Status Version 9
I just updated to V9, had an existing PO and now have added additional lines to the PO. When I print the new lines do not print, we do not have a release date (have tried adding a prior date) and still does not print on PO When I look at the line item detail, it shows line item status of 'New'. all prior 24 lines have 'Released) How does this change from new to released? -- Doug Hi Doug, Give this a shot! Go to Transactions > Purchasing > Edit Purchase Orders. - From here you will input your PO number and it will display all line item status's on your PO, ...

Emails from Russia are Blank
This is only happening with one particular individual, so I'm assuming it's something configured on her Outlook/Office and not a real problem. She's running Outlook XP and emails coming from Russian addresses do not display in her Outlook. They show empty messages. She must go through a webmail application to be able to see them. What settings or config can I check on her Outlook, Office or PC to see why this might happen? Thanks, Scott Could be her View, Encoding needs changing. "Scott Moseman" wrote: > This is only happening with one particular individu...

Can't delete e-mail #2
I am desperate -- I have not been able to delete several messages from my inbox. I get the message: The messaging interface has returned an unknown error. If the problem persists, please restart Outlook. I really need to get rid of the old e-mail since the Inbox is filling up like crazy. Please, can anyone help me? It's a shot in the dark but have you tried running ScanPST.exe on your PST file? "Alyssa" <info@advol.com> wrote in message news:06a901c366c0$b558b730$a601280a@phx.gbl... > I am desperate -- > > I have not been able to delete several message...

Mysterious Line Have Appeared on My Spreadsheet!!
Hi, I was working on my Excel 97 spreadsheet, and noticed some mysterious lines appeared after I accidentally hit the print icon. Here is a screenshot: http://mallology.lunarpages.com/excel.gif The unwanted lines are under row 424, and after Columns J and M. I do not know what these are, or how they appeared. How do I get rid of them? Thanks! Julie They indicate where your page breaks are. Choose Tools/Options/View and uncheck the Page Breaks checkbox. In article <dc2dnZ807uzsQ4_cRVn-sA@comcast.com>, "Julie P." <jporpiglia@hotmail.com> wrote: > Hi, I was w...

RMA Line Process Return
One user continually gets "The stored procedure glpBatchCleanup returned the following results: DBMS: 0, Microsoft Dynamics GP; 20486" ane the more info says 'glpBatchCleanup: Error occurred attempting to update the Batch_Headers' This error occurs when they are posting within the RMA Line Process Return. It does seem to be user specific. The KB article on this error talks about posting to historical year (they are not) and option 2 has you run scripts against SY0050 and SY00800 and dex_locks...etc. I can't have them do this all the time. THere has to be an underly...

Problem deleting "Unknown Album" from WMP V12
I have an "Unknown Album" showing with one "track" with no name, no length, nothing. Can't edit, can't delete, can't find source location. What might be causing this? What to look for in the way of corrupt file or the like? Such 'ghost' files are a common issue with WMP 12. It's probably a bug, and it could have been caused when a file in your library got deleted while WMP wasn't running, although that might be just one of the possible causes. Restoring the media library will get rid of the file: In WMP, Tools - Advanced - Restore...

missing lines when printing...
In a forward I got: >What would cause a document to not print correctly? John >is printing a spreadsheet from Excel that he received in >an email. Some of the fields are being omitted from the >printed copy vs. what we can see on the screen. He says >that it is happening in some of the spreadsheets he >creates himself. Any ideas? He tried printing it on another printer, and those seemed to work fine he said. Any ideas anyone? Every printer has its own specifications for printing, that is the reason that if you have no printer installed you can't see a print...

Using VBA to define the ControlSource property of an unbound field
I have a text field in a subform which should display a result based on the user's selection in a combo box (cboEndDate) in the main form. I've gotten the code written in a sub named daycalc(), but can't figure out how to get the subform to recognize this code as its controlsource. It's just simply blank. How do you get the field in the subform to recognize the VBA as its controlsource? If you need more details, I'm glad to provide them. I'm hoping that this is a simple thing that I'm just overlooking. Thanks, Steve. daycalc() is a sub. I do, however, have a...

Auto Delete of Sold Securities
MoneyPlus, like previous versions, auto deletes the record of sold investments after 30 days. The investment is gone even from the list of investments available to delete. After 30 days, the investment won't display in Portfolio Mgr (w/display closed positions set on), nor in relevant Reports. Transactions still display in the Account Register and in tax Reports, but one can't track performance by account or investment type because the sold investments won't display in any of these Reports. Is there a way to change this? Thank you. ...