Questions tagged [excel]
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
1,108
questions
0
votes
1
answer
24
views
Loop through cells of a range and convert to a hyperlink and change (Text To Display) to an ascending number per each row
I have used the below code Loop through cells of a range and convert to a hyperlink and change (Text To Display) to an ascending number per each row.
it works, but for the point of ascending number I ...
- 725
4
votes
2
answers
74
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 ...
- 83
0
votes
0
answers
16
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
1
answer
172
views
Extracting data stored in a JsonDocument from an Excel file on sharepoint.com, retrieved using Microsoft Graph
I retrieve data from an Excel file located on sharepoint.com using Microsoft.Graph like this:
...
CommunityBot
- 1
1
vote
0
answers
47
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 ...
- 26.6k
5
votes
2
answers
108
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
3
votes
2
answers
272
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 ...
- 1
3
votes
3
answers
385
views
VBA Vlookup to work faster
I have a code in vba that works well but it is slow so I am looking for a better solution to this code.
If there is any one who could help me I would be grateful!
...
CommunityBot
- 1
4
votes
1
answer
1k
views
Excel Chart - Crosshair cursor lines with VBA
Submitting for expert review.
I am trying to create a Chart - Crosshair cursor in excel chart sheet. Chart - Crosshair cursor lines are two lines (one horizontal and other vertical) moving along with ...
CommunityBot
- 1
2
votes
2
answers
137
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 ...
- 1,371
8
votes
1
answer
2k
views
Spreadsheet to calculate how to cut pipes
I have written a piece of code that calculates how to cut pipes based on an order and fixed raw pipe length (in this case 288 feet), but I am not 100% happy with my current code as it counts the pipe ...
2
votes
1
answer
82
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 ...
- 2,335
-2
votes
2
answers
67
views
Macro which performs a series of operations on a given set of data
This macro calculates some basic figures from a given set of data. But it runs very slow probably due to me referencing the sheet again and again. Does anyone have a better idea on how to optimize ...
- 69.2k
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+...
- 22.4k
6
votes
3
answers
693
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, ...
- 209
0
votes
1
answer
58
views
VBA Excel - autosum values from all workbooks in the directory
I have the code such as this for opposite conditions.
...
- 352
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 ...
- 264
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. ...
- 264
3
votes
1
answer
89
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 ...
- 1,217
1
vote
1
answer
129
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 ...
- 1,217
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 ...
- 121
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 ...
- 1,217
3
votes
1
answer
125
views
modArraySupport: refactoring `IsArrayAllNumeric` and `IsVariantArrayNumeric`
Foreword
I try to learn unit testing in (Excel) VBA using Rubberduck. As an example "project" I want to create tests for Chip Pearson's modArraySupport and afterwards refactor that module, ...
- 135
3
votes
2
answers
890
views
Excel Multiplication Table Maker
I did the following exercise from here to try out the openpyxl module for generating excel files:
Create a program that takes a number N from the command line and creates an NxN multiplication ...
Mast♦
- 13k
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 ...
- 1,217
2
votes
1
answer
145
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 ...
Mast♦
- 13k
2
votes
1
answer
958
views
OOP Cross Sum Solver
Not satisfied with the Brute Force Solver that I wrote for All possible combinations of 1 to 9 in the same cells without repetition, I created an OOP Cross Sum Solver.
As expected my OOP Solver ...
22
votes
1
answer
27k
views
Snake in Excel in... VBA?
snake-game is a game where you control a snake in an enclosed square without running into the walls or your own body. The snake grows when you run into whatever item you are supposed to run into.
My ...
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 ...
- 4,113
2
votes
0
answers
174
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 ...
- 143k
2
votes
1
answer
120
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 ...
- 8,099
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.
...
- 26.6k
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 ...
- 26.6k
3
votes
1
answer
222
views
VBA syntax that compiles sub data into master sheet
I am using this syntax to bring in the names of all employees whom are active, then from there filter the data on subsequent worksheets and use a formula to bring the data over to the ...
- 245
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 ...
- 5,954
4
votes
2
answers
519
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 ...
- 1,371
18
votes
0
answers
729
views
Progress Bar for Excel
Any future updates can be found at Excel-VBA-ProgressBar
What I've been using so far
For the last 6 years I've been using a progress form which I wrote in VBA using just Windows API calls. The code (...
6
votes
1
answer
389
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, ...
- 1,371
0
votes
0
answers
131
views
creating worksheets for unique values parsed
I have built some code that should parse the data according to a unique value and then create a new Worksheet for each unique value. My initial table has 10 Columns and around 25K rows. The code works ...
- 26.6k
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
979
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 ...
- 1,217
5
votes
1
answer
1k
views
Compare two Excel sheets by Cell Content
This code is a result of a lot of help from this community.
What does it do?
It compares two excel sheets cell by cell and copies the differences into another excel file.
I’m trying to modify the ...
- 1
-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. ...
- 1,371
0
votes
0
answers
260
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
465
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
4
votes
5
answers
396
views
Turn On/Off Functions to speed things up
I have these two macros that are like my Yin & Yang. They turn off and on functions in excel so my macros calculate quicker.
I call on in the beginning and one at the end.
Is there anything I ...
- 446
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 ...
- 56k
3
votes
1
answer
236
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 ...
- 1,371