Questions tagged [excel]
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
1,107
questions
1
vote
0
answers
13
views
Efficiently creating an array from a filtered sheet
[edit] I realised why everyone does this with loops; my plan was to use .SpecialCells(xlCellTypeVisible).EntireRow.Address to return the row(s) of the results of the results of the filter, but that ...
- 11
0
votes
0
answers
14
views
Speeding up VBA to add/remove Pivot Tables from Slicers in Excel
I have a workbook that has a number of Pivot Tables spread across a number of worksheets. There's a bunch of slicers on one of the worksheets, and they're pretty slow-ish to load after changing ...
- 1
0
votes
0
answers
27
views
Python script to read an XML file and write some information to an Excel file
I'm getting an XML file from an application, want to extract some information from that file and write it to an Excel file. The XML file contains data about vacations. I need data from the last month ...
- 101
1
vote
0
answers
46
views
Appending data from files listed in a file
I am working on a project that requires me to write VBA code which, upon a button press, loads the data from certain files in a given directory.
In my testing, I was able to load the data from 1100 ...
5
votes
2
answers
107
views
concatenate values and delete the remaining rows using arrays
the below code used to:
Concatenate the values on a specific column "N" depend on the value of column "A" then delete the remaining rows.
It works, but with range of 30k rows the ...
- 265
2
votes
1
answer
80
views
Update an Excel master workbook with values from new workbook
I have a master .xlsx file on which other spreadsheets depend. Each week, I export a new .xlsx file (from MRP) that may or may not be the same as the master. The columns are always identical. Rows may ...
- 23
1
vote
0
answers
37
views
Inserting Rows into Access Table Using Excel Macro
I have a macro that inserts data from Excel into an Access table. Each time it runs there may be 10k -250k rows to insert. I have experimented with using an empty database/table but it still takes 60s+...
- 123
0
votes
1
answer
57
views
VBA Excel - autosum values from all workbooks in the directory
I have the code such as this for opposite conditions.
...
- 259
3
votes
1
answer
89
views
Iteratively paste values from spreadsheet tabs into empty spreadsheet
Basically below code is taking first 7 tabs from my current sheet and pasting them as values in a blank excel sheet with same tab names and saving them in a specific folder by filename I specified. ...
- 31
3
votes
1
answer
86
views
Excel VBA to powerpoint
I am basically looking for a way to trim below code. It works just fine.
This code takes a range from each tab of an excel spreadsheet and pastes it into a powerpoint file, then assigns a title to ...
- 31
2
votes
1
answer
128
views
Copy and Paste Macro Containing For Loop
The macro below loops through column E in Sheet2 and finds matches in column Z of Sheet1. If a match is found it copies the value on the same row contained in column AA.
It works ok but hangs when it ...
- 123
1
vote
1
answer
122
views
Optimising Read/Write Speed of Excel VBA Copy/Paste Macro
I have an Excel sheet that connects to third party software which populates Sheet1 with data. It does this multiple times per second and overwrites previous data.
I have written the macro below to ...
- 123
4
votes
1
answer
165
views
Using a Counter to Select Range, Delete, and Shift Row Up
I'm trying to clean my excel sheet so that all lines match with each other when I put 3 different tables from 3 different data sets next to each other. The code I have gets the job done but it's ...
3
votes
3
answers
196
views
VBA Very Slow Performance using objects with scripting dictionary
I have a simple code that loops through a text file to fix a few things in it and I use mostly dictionaries to find and handle the adjustment. However, with larger files (about 30K lines) it seems to ...
- 31
2
votes
1
answer
69
views
Inserting Rows after Contiguous Set of Integers - Excel VBA
I have a repetitive data reduction where I have a list of ID's that I want to find in a longer list. I have a method, but it is slower than I'd like. How can can I speed this up?
The process: After ...
0
votes
1
answer
53
views
Code compares Columns "A" of two workbooks and copies different information to destination workbook with entire selected row. LastRow count slows code
Code explanation:
I have a code, which performs two tasks -
To open two workbooks, one being extract info and one destination and it compares the column A with Column A of these workbooks and all ...
- 3
2
votes
2
answers
136
views
Sub that calls itself when its parent Workbook is closed
and no, I'm not talking about Workbook_BeforeClose. Note that the motivation of this question is Excel specific but the technique I'd like feedback on is not.
If ...
- 165
2
votes
1
answer
144
views
Create a string "Build" number for procedures and module
The class assigns various strings to the build number. So the build might look like 3.4.5.1 which would be of the format ...
- 121
2
votes
0
answers
165
views
SheetJS method to create an Excel file with subheaders
The method is fully functional and creates an excel as expected.
What I would like to know, is if can be made more functional and better written for maintainability.
For example, how can I put the ...
- 271
4
votes
1
answer
182
views
Optimizing a large report sorting macro
I've been working on this project for my organization for a few months now, and am looking for ways to increase performance on this macro.
I work for an insurance company as a claims auditor. I get a ...
2
votes
1
answer
117
views
Parsing information from Excel files
I have to parse information from several Excel files (.xls, .xlsx). The structure of the files is nearly identical except for the columns order. In this case it is ...
- 699
0
votes
0
answers
43
views
Update cell format based on adjacent cell values
Trying to speed up this macro, it just loops through 'C-L" columns and changes the format based on adjacent cell values.
...
1
vote
0
answers
40
views
makes plots associated with selected utilities
This code is applied to a master workbook with tabs containing characteristic information e.g. "Reliability standards", "Targets" etc. about each utility. The utilities of interest ...
- 11
1
vote
2
answers
80
views
Copying formulae between sheets
I am currently running multiple macros on a large volume of files however watching it go through its processes today I realise one of my issue is that my loop runs through my workbooks 3 times instead ...
- 21
4
votes
2
answers
514
views
Loop between two Arrays and change values based on condition
Instead of using 45 IF conditions , I put my two ranges into variant arrays.
Then I used the below code to loop between them and change values of the first array ...
- 265
6
votes
1
answer
388
views
Fastest function to `Remove Duplicate Lines` per each cell
The below function is used to Remove Duplicate Lines per each cell.
It works without problem, but it is slow with a range of only one column and 17k rows.
Actually, ...
- 265
1
vote
0
answers
43
views
A Type.FromText function in M for PowerQuery using Expression.Evaluate
The Solution
I recently came across this solution here, for a Type.FromText() function in M for Power Query*. This solution allows a user to extract the actual <...
- 359
9
votes
2
answers
966
views
Small datasheet code running super slow on Excel VBA
The code has been super useful with my work datasheets.
They all have this pattern and the VBA code makes sure it all has the right format.
Why is it so slow?
Sometimes it takes 2~3 minutes to run a ...
-3
votes
1
answer
72
views
Transpose and Paste VBA [closed]
Currently from my sheet2, I wanted to copy the column C36-N36. However, each of these cells has one formula that is generated from the numbers within sheet2, i.e. C36 = A1+A2/2 something like this. ...
- 5
0
votes
0
answers
250
views
Export dataGridView to an Excel Worksheet in c#
I have a dataGridView in a WindowsForms. I also have a button to export this dataGridView to an Excel Worksheet. If the button will be clicked, the data in the dataGridView will be exported to a XLSX-...
- 130
6
votes
1
answer
461
views
Filter Range VBA function for Excel
Premise
I have written a function in VBA, called FILTER_RANGE(), which accepts a Range object and a filtration parameter. Unlike ...
- 359
2
votes
1
answer
79
views
Gathering data from a file and analyzing it
Recently started learning python, and it's quite a difference coming from C and knowledge taught in CS1. Overall, I am trying to build a team building script for my summer side-project (probably will ...
- 23
3
votes
2
answers
269
views
VBA functions to calculate sun and moon positions
I have the following VBA code (across three modules) to make UDFs to calculate sun & moon position data. The issue I'm facing is that they are very slow to run as I have over 6000 rows (over 10 ...
- 31
3
votes
1
answer
228
views
Add padding to top and bottom of cells
I have a column with variable length text in each of the cells. I have written some code to add padding to the top and bottom of the cells. While the code works well it becomes quite slow when the ...
- 131
1
vote
0
answers
57
views
Extract, transform, and load Excel files
I am curious to know if there is a cleaner/better way to write this. Any suggestions would be greatly appreciated! I do ask some questions in comments within the code specific to certain functions/...
- 111
2
votes
1
answer
62
views
Identify Anonymous orders in BOM list & update accordingly, execute time is taking very long
Right here, I'm trying to identify whether or not the demand order in my multi-level BOM list is made by an anonymous customer and then update accordingly.
-In laymens term, I'm checking if an item ...
- 55
2
votes
0
answers
249
views
Command line framework for VBA compiler app, written in twinBASIC :o
Ok compiler is a bit of a stretch...
I'm working on a package manager for VBA code, written primarily in python. One fiddly step of the pipeline involves "compiling" packages; this is the ...
- 2,335
0
votes
2
answers
130
views
VBA - Compare & Consolidate Dynamic Arrays
Background
The CSV files being used can be 6+ mill records in size, using a small sample size to proof the methodology.
Why are you doing this on excel? Access or SQL would be quicker
My company has ...
- 11
8
votes
0
answers
332
views
A VBA program to quantify the performance of code execution timers
Motivation
I am interested in optimising the execution time of code with short (fast) execution times on the order of microseconds (µs), but that will be used with high frequency (e.g. like ...
- 181
2
votes
1
answer
74
views
VBA, Data Split Into Two Rows, Convert To One Loop Optimization
I'm trying to convert a data set that is split into two rows into a single row. This was my solution.
...
2
votes
1
answer
557
views
Excel VBA to Fetch email addresses from inbox and sent items folders
I am using the following VBA to fetch multiple specified Email addresses from inbox and sent items folder also including cc and bcc
for eg(gmail.com;yahoo.com) must return all mails having that domain ...
2
votes
0
answers
49
views
Python data entry into excel based on equality check with dataframe
My objective is to enter/type in values into Excel cells if the column names and indices match between Excel and dataframe.
So, my code does the below
Gets the ID values from the dataframe
for each ...
- 237
0
votes
0
answers
82
views
Load license plate data from Excel
The code below gets data from an Excel spreadsheet and returns some values; this can take up to 15 minutes because the Excel has more than 15 columns and an average of 20,000 rows.
...
- 11
3
votes
1
answer
234
views
Automating shortages report from BOM list in excel but the total run time to output the report is quite slow. Using Excel 2010 - CLOSED
I MIGHT REWRITE THIS QUESTION IN A CLEARER WAY WITH SOME SAMPLES TOO AND POST IT AS ANOTHER SOON, AS NOW MY EXPLAINATION HERE IS QUITE HARD TO UNDERSTAND & MIGHT BE PHRASED WRONG.
I do have ...
- 55
0
votes
1
answer
64
views
importing excel data by matching and performing lookups
This code imports data from a separate sheet and matches it based on Column E's data, it does this for every cell then offsets to the next one (622*6) times. If there are any substitutions for vlookup ...
4
votes
2
answers
202
views
Scraping campsite availability from a webpage using vba with selenium
I wrote some code to extract the information from a table, but it takes an extremely long time.
The table is in the format of a calendar.
I need the information on an Excel sheet with column 1 as the ...
- 41
6
votes
3
answers
683
views
Safe Windows API Timers for Excel
Update Feb 2023
This solution is no longer maintained here or on GitHub, mainly because I do not need a timer solution anymore. As seen below, the only purpose of this approach was to get a reliable, ...
- 1,371
3
votes
0
answers
120
views
Updating VBA code from a central repository in distributed Excel workbooks
I wrote this to help manage the code in a bunch of distributed workbooks. Maybe someday I will re-write this as an add-in, but for now, I just needed a way to manage the code without having to ...
- 409
1
vote
0
answers
217
views
Get & change file names from folder
This was originally posted as a Stack Overflow answer.
Objective
Import all filenames from a folder & change the filename using a mapping table.
Challenge
As described in the original posting, ...
- 11
0
votes
1
answer
118
views
Python - Efficiently pick random data from an array, generate random UUIDs and save it all in an Excel table
I wrote the following prototype:
...
- 195