Excel Tips View, Edit and Delete range names well done XL 2007

I know how to create and name a custom view. I also know how to change to one of my named views. But I do not know how to cancel a current named view and revert to the previous (unnamed) view!

There is nothing in the Excel Help system or any of the literature that gives any guide to this problem.

Do I just have to remember what options and restrictions are contained in the named view and reverse each and every one individually?

Dear Friends,

I have big file with lot of data in excel 2007. And I want to delete some of the records from that list. When I want to delete a row I view the data in auto filter view and check each row and select the unwanted rows each time and delete entire row.
Now my question is when I view in filter mode is it possible to delete a row directly from filter view. For Example I filter the data and select the A1 cell and viewing the data by pressing Alt+Down arrow.. at this time I want to delete unwanted rows.
Please help in this regard. and Thanks in Advance to all.

A.Ramu
India
9989932120

I have a pivot table using a named range as the data source. I had to add another column to the data, so now my named range needs to be expanded.
In "Formulas" | "Name Manager" this "Name" has an icon that looks like a little table. I have other "Names" and they all have icons that look like little name tags. I can edit the ranges in the ones that look like name tags, but the range is greyed out in the one I need to edit, with the icon that looks like a table.
I don't know how I created this one (it's been a while). I created a new named range with the range I need and it looks like the others.
The reason I need to edit this one instead of just using the new one is when I change the range from the old one to the new one it destroys my carfully crafted pivot table. All the correct fields are there, of course, but none are selected so I'd basically have to start over.
Anyway, why does the one have a different icon and why can't I edit it's range?

Hello All,

I'm hoping someone can help me out with something I've been having trouble with. Within Excel, it is possible to give a Range a name. In a VBA macro for example this can be done by doing the following:

ActiveWorkbook.Names.Add Name:="adfasdf", RefersToR1C1:="=Sheet1!R4C2"

Similarly, the name for this range can be deleted by doing:

ActiveWorkbook.Names("adfasdf").Delete

My issue is that I don't want to use those macros, I want to program using the C# Office Interop tools. I can easily add a name by doing something like:

destWB.Names.Add(...);

and filling in the parameters, that is no problem. When it comes to deleting however, I just can't get it to delete. I use the following code:

((Excel.Name)destWB.Names.Item("Test", Missing.Value, Missing.Value)).Delete();

where destWB is a Workbook. There is also a name in my sheet called "Test". The name just won't go away! I've looked up a lot of other ways of doing it. Usually doing the same as the VBA macro will work (slightly changing syntax), but not this time. Hopefully someone has some input. Thanks a lot for your time,

Luc Gallant

Hi,
More often than not the various solutions posted for manipulating charts in Excel 2007 with VBA are issue specific, which may be adapted by other users to suit their needs.
This is great, but often means trolling a large number of threads and posts before you find something you can use/adapt.

Given that chart methods are very difficult to track down and the VBA chart object model in XL07 isn't terribly helpful, and to possibly help users with basic manipulation I've started this thread in the hope that others will contribute snippets of code for making changes to charts in XL07 using VBA.

To start the code below can be used to make the chart border invisible for every chart in the active sheet.

Code:

Sub Remove_Chart_Border()
' Use this to make the border on each chart in the active sheet invisible
'
' Get the number of charts in the active sheet
NumberOfChartsInActiveSheet = ActiveSheet.ChartObjects.Count
'
'Create a For/Next loop to cycle through each chart in the active sheet
For ChartLoop = 1 To NumberOfChartsInActiveSheet
'
'activate the next chart in the loop
ActiveSheet.ChartObjects(ChartLoop).Activate
' Do something with the active chart
With ActiveChart
' In this case the "do something" is set the line of the border of the Chart Area to invisible (visible = false)
    .ChartArea.Format.Line.Visible = msoFalse
End With
'
'move on to next chart in the loop
Next
'
'
End Sub

My knowledge of manipulating charts with VBA is very limited, so I'm hoping other (likely more experienced) forum members will see this as a good idea and tag on.

I'm no expert but I'm a huge fan of pivots and thought I'd share a couple of things I use to help format them and get around certain shortcomings.

Splitting up values and supressing totals on certain columns.

My report required grouping of columns and then a gap between those columns and for certain columns of values to not be summed. My solution below.


By toe

Another thing I found out is the ability to mix tabbed fields with compact fields. That allows you to create the effect I have above with the 'Investments' and 'Equities' headers as compact and the further detailed fields as tabbed.

I also used a macro to hide the pivot headers.

My goal was to create a dynamic report but make it not look like a pivot at first glance

Any tips you guys would like to share?

Submit your Tips & Tricks in this thread to help new users get up to speed!

Hi,

I'm an avid creator / user of dynamic range names, but when i create/define
them, they do not appear in the list of named ranges when I choose
'Edit' -> 'Go To' from the menu bar. Is there any way i can force them on
to that list? i have created a userform that i use to easily create my
dynamic ranges, so it would not be a problem to include extra vba to put
them into the 'Go To' list, if i only knew how!

any advice greatfully received!

tim

Hi

I have some code to email a single, different excel file to multiple Outlook recipients. I am using Outlook 2003, and Excel 2007. I always use Compatability mode in Excel as 2007 is not widely used.

The code is shown below. What I can't understand is why it fails at the code highlighted red. It tells me that there is Run-time Error '13': Type Mismatch, but I can't see the issue. I'd be most grateful if someone could help me please.

I've also attached a quick example of the data from which I am trying to create the mails. The name in the file is fictitious.

Code:

Sub Send_Files()
'Working in 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set sh = Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon

    For Each cell In sh.Columns("G").Cells.SpecialCells(xlCellTypeConstants)

        'Enter the file names in the H column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("H1:H1")

        If cell.Value Like "?*.?*@?*.?*" And _
           Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
                .to = cell.Value
                .Subject = "eSales Data To Be Validated & Entered"
                Dim strbody As String
                strbody = "Attached is your Excel forecast data as at Thursday 15th October." & vbNewLine & vbNewLine & _
                "Currently, eSales does not agree to this data.  We require you to ensure that" & vbNewLine & _
                "your eSales data matches exactly what you are reporting in the Excel spreadhseet." & vbNewLine & vbNewLine & _
                "Please use this data to check and update your eSales opportunities." & vbNewLine & vbNewLine & _
                "This is line 2" & vbNewLine & _
                "Please focus on Q2 and Q3 at this time.  Please complete your entries for Q2 by Friday 30th October." & vbNewLine & _
                "Please complete your entries for Q3 by Monday 16th November." & vbNewLine & vbNewLine & _
                "You should already have received details on how to treat split deals and the rules surrounding these in eSales." & vbNewLine & vbNewLine & _
                "Thank you for your co-operation." & vbNewLine & vbNewLine & _
                "Regards" & vbNewLine & _
                
                
                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                         If Dir(FileCell.Value) <> "" Then 
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell
                
                    .Send  'Or use Display
            End With

            Set OutMail = Nothing
        End If
    Next cell

    Set OutApp = Nothing

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

My excel 2003 now does not show the top line with the options: File, edit,
View, Insert, Format, Tools, Chart, Windows, Help
Dont find how to retore it, if press F11, they appear in a chart that
automatically is add to workbook, but other sheets will not show it.
Without this cannot use excel.
Please advise how to restore it.