Microsoft Excel

135K

Solutions

229

Content

i
The collection of articles, videos, and posts on this topic.

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a Excel user using 2016 Professional 64bit ….     her spreadsheets are fairly large and with 50K plus rows in multiple tabs.    Her computer is running and i7 processo with 32mb of memory and a M2 solid state drive …..    

Some of the time when she goes to filter, sort or create a pivot table the program locks up and eventually closes excel.    I have looked on the web for settings to increase the performance of Excel with lots of data and was able to disable hardware acceleration which helped for a bit …..

Any thoughts on additional excel settings to solve the issue ….

Joel
0
8 Comments
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Learn More!
Hello experts,

I have the following procedure used for adding specific character in a column at the end or at the beginning of a string.
Sub Add_Specific_Char()
Dim rng As Range
Dim strSpecificChar As String
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim intWhich As Integer

    intWhich = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
            
    strCol = InputBox("Please report column letter in which you want to apply procedure", "Choose Column Letter")
    Set rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)

    lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row


    If Not rng Is Nothing Then
    
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Select Case intWhich
                Case 1
                    Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
                Case 2
                    Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
            End Select
        Next

    End If
    
End Sub

Open in new window


I would like to revise it with the following requirements:

1-Properly exit sub when user click on cancel for the various inputbox set up in the procedure.

If you have questions, please contact me.

Thank you very much for your help.
0
2 Comments
I need to insert a date slider for my pivot table that will make the number changes based on the slider.

Please see attachment.
DateSlider.xlsx
0
3 Comments
I have list of hours (Interval)  and I need to start at 1/1/2019 add the hours and identify another date in the future.  Does not have to be exact. Only go to the next day if a full 24 hours has elapsed.

Interval      Start Date      End Date
12             1/1/2019              1/1/2019
24             1/1/2019              1/2/2019
50             1/1/2019      
60             1/1/2019      
168             1/1/2019      
240             1/1/2019      
500             1/1/2019      
672        1/1/2019      
730             1/1/2019      
1000      1/1/2019      
1460      1/1/2019      
1500      1/1/2019      
2000      1/1/2019      
2160      1/1/2019      
2184      1/1/2019      
2190      1/1/2019      
2500      1/1/2019      
3000      1/1/2019      
3400      1/1/2019      
3500      1/1/2019      
3700      1/1/2019      
4032      1/1/2019      
4380      1/1/2019      
4500      1/1/2019      
5300      1/1/2019      
6000      1/1/2019      
7400      1/1/2019      
8064      1/1/2019      
8760      1/1/2019      
10000      1/1/2019      
15000      1/1/2019      
16000      1/1/2019      
17520      1/1/2019      
20000      1/1/2019      
25000      1/1/2019      
35040      1/1/2019      
43800      1/1/2019      
52560      1/1/2019      
87600      1/1/2019
0
2 Comments
So I have my import tool working great. It allows the user to browse to the excel file and fills in the file name txt file (txtFileName)
Importing the date to the table works great.  I can even have it do all the sheets of the workbook.

I was wondering if there a way to browse within the workbook to display just the worksheets and the user can select what worksheet they want to import and it stores that worksheet name in the txtWorkSheet.

Thanks
john
0
5 Comments
I have received a bizarre set of data in an xlsx format, with what seems to be some entries in over 150 columns or so. By using auto filter, 99% of the cells are blank but there are a few entries in each column, at completely randow rows. What I want to do is to collate all the cells of data into a single column, rather than having to manually autofilter each column one at a time, filter out blanks, copy the actual entries then append to the bottom of a master column of all data. Can this be done easily in excel? Obviously I dont really want to copy over several thousand blank cells each time into the master colum,n, only those with an actual entry.
0
2 Comments
All,

I have a test sheet based off another sheet that I have. What I need to do is have the 3 columns display only the data that references what is in the lookup field. For example, if "xyz" is in the error column, I need the data for the 3 columns to display on the lookup sheet, and display all rows.
sampleBook1.xlsx
0
3 Comments
Hi

On my ASP.net web page I want a user to download certain information that pertains to their user ID
in an Excel spreadsheet.

I currently use the following code to allow a user to download an Excel file, but I want to rather populate
a spreadsheet using SQL code and have that open

    Protected Sub btnDownLoadExcelTemplate_Click(sender As Object, e As EventArgs) Handles btnDownLoadExcelTemplate.Click
        Try

            If True Then
                Dim FileName As String = "Steel.xls"
                Dim response As System.Web.HttpResponse = System.Web.HttpContext.Current.Response
                response.ClearContent()
                response.Clear()
                response.ContentType = "application/vnd.ms-excel"
                response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & ";")
                response.TransmitFile(Server.MapPath("~/Resources/Steel.xls"))
                response.Flush()
                response.[End]()
            End If
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

Open in new window
0
2 Comments
I'd like to query a REST API (for harvest, but I guess it is a pretty generic question) from Excel 2016.

I have all the parameters I need to pass, ie
curl -X POST https://api.harvestapp.com/v2/tasks \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Harvest-Account-Id: $ACCOUNT_ID" \
  -H "User-Agent: MyApp (yourname@example.com)" \
  -H "Content-Type: application/json" \
  -d "{\"name\":\"My New Task\"}"

Open in new window


But how do I use the Excel built-in wizard for this ?

Excel settings
0
8 Comments
format a  date/time   column
These dates are just examples
I have a column with these dates : "General Format"


01-AUG-18 01.17.52.000000000 PM

I need it displayed/formatted as  date/time ?

2018-08-01 01:17:52 pm


no matter what i do the cell data will not change and there is no tick mark in the cells ?


Any help appreciated.

Thanks
fordraiders
0
12 Comments
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Enroll Today!
Need to save excel to csv, comma delimited with double quotes surrounding each column.
0
2 Comments
I am using the function below using a named range to get the sheet name in a closed workbook.Is there a better way.


Function GetSheetsNames(file, RAN_GE As String) As String: Dim wb As Object: Dim wsht As Excel.Worksheet
    On Error Resume Next
 Set wb = GetObject(file): Set wsht = wb.Worksheets(wb.Names(RAN_GE).RefersToRange.Parent.name): GetSheetsNames = wsht.name: wb.Close False: Set wb = Nothing
End Function
0
1 Comment
In the attached example, I need to copy range B2:E2 and then paste the values to the right of todays date that is located in column G.
Example-File.xlsx
0
4 Comments
I have a large 300,000 row CSV file that I need to split into 4 CSV files. When I open this CSV file in notepad, I can see that the leading 0's are there for our part numbers: (000006446) for example. Excel by default truncates the leading zeros from view when I open the CSV. That's fine, I don't need to see them there. The problem is that when I use Excel to modify this CSV file and save a copy, then Excel updates the CSV file and removes all of the leading 0's from the file.

Does someone have a creative idea on how to preserve my leading zeros? I can't just convert the column to Text format because the leading zeros are already dropped from the view in Excel, so this too removes all of the leading zeros.
0
12 Comments
I would like to do a function in SQL that uses a regex string to validate email addresses and which also is not going to bog things down.
0
3 Comments
My brain just froze. I would like the AND/OR formula for the following please
C1 MUST Contain "RED"
D1 can contain "APPLE" OR "PEAR"
Destination cell A1
Many thanks
Ian
0
8 Comments
I know how to use VBA code to import data from an excel spreadsheet to an access table.  All of it work great.
How do I tell the code that the headers are on row 3 and skip rows 4 and 5.  If its possible.
0
10 Comments
See purple cells in:
Forecast sheet rows 72-79 AND Schedule sheet rows 59, 60 and 62

I need the number of agents to read over from the Forecast sheet to the Schedule sheet. We would add more agent names, between row 63 and 85 on the Schedule sheet, for example. Just an FYI, so the formulas do not mess up.
Staffing-Report.xlsx
0
0 Comments
I have two pivot tables that I need to control with one slicer. One of the tables is current year data. The other is last year data. How do I get the pivot table to change the year to the previous year using just one slicer?
0
5 Comments
JavaScript Best Practices
LVL 12
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
How can I set up a formula that calculates a total from a range based on matching a date from a dropdown. I've been experimenting with Sumifs but can't get it to work.. In the attached file I have entered what I want the answer to be depending on what I choose from the list. Thank you as always.
EE_Cumulative_YTD_Show.xlsx
0
5 Comments
Attached is an Excel document, Excel_EqualWidthCells.Xlsx that has two sheets, one labeled "Structure".
The other is labeled, "Header-Footers", and looks like this:
Headers	Footers	 -- There is inconsistent use of "…", so assume that "…Begin" and "Begin" are keywords
…Begin	…End	 -- Notice the "…" acts like it is a singe char
…If		         -- Assume case insensitivity, so "BEGIN" and "begin" are also matches
…For

Open in new window

For simplicity, I can remove the "…" from the document. Other tables have different keywords for their headers/footers, so I'll just change the corresponding headers and footer keywords in the VBA code.

The attached Excel_EqualWidthCells---ManualInden.xlsx document shows the desired result after the indentation macro is run.

The Excel_EqualWidthCells.xlsx file is very flat looking, yet describes a complex structure using headers and footers. I manually indented sections of rows so that I could see the structure. This is tedious, but doable.

One suggested approach in a different question is found here.
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42769597
"This can be done with a stack structure, where you push a set of items on the stack when some condition is detected and remove them when another condition is detected.  In your case the first set of conditions is a cell that begins with ("loop", "if") and the second set of conditions is a cell that begins with ("end").  If you're working in Excel, you might use the stack 'level' (depth) as a parameter in an .Offset() method.

"With as simple a problem as this one, your stack structure can be an integer variable.  You might indent as you iterate the rows."
…
0
31 Comments
Hi,

Have Excel 2016 workbook, With an Entity Volume (here in column "C" sometimes other columns), problem is that the software I got the data from have added string " cubic m" to the values and to sum up the data in the column I need to remove the added string With a click of a button. How do I get round that?
0
6 Comments
Attached below is a Word doc with a sample table that I try to copy to Excel. One row in the Word table may become multiple rows in Excel. That causes other problems when processing the Excel table. Ideally, the copy to Excel gives one row for each ACTION FIELD entry as it does in this Word table.

Word Table showing first 8 rows:
Shows first few lines of Word Table
Excel Table after copying first 8 rows has 22 rows. I would like Excel to have only 8 rows, since that is what Word had.
Shows first few lines of Excel Table - too many rows
Both the full sample Word table, and the attempted copy to Excel (with 22 rows) is attached.
Word_to_Excel_Issue.docx
ExcelHasTooManyRows.xlsx
0
7 Comments
long list of rows like the one in the attached in row 1.

i need to split it into two rows as i did in the below cells. sometimes three rows if there are 3 entries in column C separated by col.

as you can see 1st entries in C,D,E go into their own new row. 2nd entries go into a new other row
Book27.xlsx
0
11 Comments
Hi

I use the following code in my VB.net Excel Addin to create a taskpane when the addin loads.
It works well in Excel 2007 and 2010 but badly in Excel 2013 and 2016. In 2013 and 2016
the taskpane disappears or causes errors.

Is there a better way that I should be doing this in the later versions of Excel?

Public Class ThisAddIn

    Private oTaskPane_Tables As TaskPane_Tables
    Public WithEvents Tables_CustomTaskPane As Microsoft.Office.Tools.CustomTaskPane


    Private Sub ThisAddIn_Startup() Handles Me.Startup
        Try

            oTaskPane_Tables = New TaskPane_Tables
            Tables_CustomTaskPane = CustomTaskPanes.Add(oTaskPane_Tables, "Tables/Views")
            Tables_CustomTaskPane.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionLeft
            Tables_CustomTaskPane.Width = 330

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub
  
    Public ReadOnly Property oTABLES As TaskPane_Tables 'gives access to alter controls on TaskPane
        Get
            Return oTaskPane_Tables
        End Get
    End Property

    Private Sub Tables_CustomTaskPane_VisibleChanged(sender As Object, e As System.EventArgs) Handles Tables_CustomTaskPane.VisibleChanged
        Try
            Globals.Ribbons.Ribbon1.ToggleButton_Tables.Checked = Tables_CustomTaskPane.Visible

        Catch ex As Exception
            MsgBox(ex.Message & "

Open in new window

…
0
0 Comments

Microsoft Excel

135K

Solutions

229

Content

i
The collection of articles, videos, and posts on this topic.

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.