Skip to main content

Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.

Filter by
Sorted by
Tagged with
2 votes
1 answer
217 views

Python using generators with Excelwriter - Performance

I'm looking to understand if my code has an obvious blockage or performance pain point that will cause it to operate slower or use more memory than it should. The current Excelfile i am processing ...
4 votes
4 answers
888 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 ...
22 votes
2 answers
29k 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 ...
-1 votes
1 answer
47 views

Need assistance modifying a "too large" procedure [closed]

I have a chunky VBA script that needs some help making it efficient, which I think will correct the "Procedure too Large" issue I just ran into. I run the procedure from an open workbook ...
0 votes
0 answers
40 views

Faster method to check if a string is found inside Word Files in a directory and subdirectories

I need to check if a string is found inside a Word File in a directory and subdirectories. I have tried to use Advanced Search of MS Windows 10, to search inside ...
11 votes
1 answer
655 views

Produce a Linear Cut List for Woodworking

I was inspired to write this by a recent review of cutting pipes. The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way to ...
2 votes
1 answer
161 views

Which is faster: Unhide rows during IF or Unhiding them all at once in another macro?

I have process where I am generating workbooks from a list. One of the tabs has varying height and I have a formula that my macro uses to determine whether to hide a row. As I move to the next ...
0 votes
0 answers
33 views

Add a new sheet for all files in folder, pull data from multiple reports for each one, save PDF/xlsx summary

I'd appreciate any help people can offer to make this process more efficient and less goofy. I've created a process to loop through a folder of approximately 100 files, add a new sheet with a base ...
6 votes
4 answers
153 views

an algorithm that chooses the best games on steam

The code below will go through an excel spread sheet taken from kaggle, is named "SteamGames (71k games)" and the creator is "MEXWELL". inside the ...
5 votes
1 answer
2k 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 ...
29 votes
0 answers
1k views

Pacman implemented in OOP VBA

What?, Why? I have been inspired by several other posts on the topic of OOP implementations in VBA to try and create a Pacman clone. I think this task is not all that hard in most languages; but, I ...
0 votes
0 answers
95 views

1d profile cutting optimization

I wanted to make a VBA that creates self-renewing combinations and I actually did it, but Excel was crashing when the combinations increased, unfortunately Excel does not work in multi-item lists. ...
3 votes
1 answer
617 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 ...
0 votes
0 answers
108 views

userform with textboxes and dates to show timeline

I am writing the following VBA code to make a timeline, which I will be using for other parts of the code later on for other calculations and output. I have a userform where the following number of ...
3 votes
0 answers
386 views

A vehicle database in Excel

I'm looking for some help. I'm new to coding and have started creating this Vehicle database program in Excel for a friend to use. He is wanting to be able to see all vehicles he owns, when ...
1 vote
1 answer
285 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: ...
2 votes
1 answer
92 views

double click on userform running slow excel vba

I have this code for picking items with multiple values from userform and inserting them into a sheet. The code works perfect on my computer, which is pretty decent. But on a computer from work it ...
0 votes
1 answer
125 views

Excel VBA - How to optimize this code with a lot of inserts

Can you please suggest how below code can be optimize? It takes around 2 minutes in Windows and 4 plus minutes in Excel 2016 for Mac. I think I will have to use write once method, accumulating the ...
3 votes
3 answers
578 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! ...
1 vote
1 answer
516 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 ...
2 votes
0 answers
431 views

Generic function for interpolating missing values in power query

I have written a (relatively) generic function for interpolating values in power query (also useful for power bi and m code). I am looking for any improvements or input, but especially: speed handle ...
0 votes
2 answers
120 views

Optimize a Python code which indicates duplicated values in an excel file [closed]

I wrote this code to indicate duplicated values. It actually works but I hope to know if there's another possible solution to optimize this process. Thanks. ...
1 vote
0 answers
86 views

Import large Excel files directly from XML streams into an SQLite database

I've got some pretty large Excel files with nearly 1M rows that are almost impossible to open and work with in Excel anymore that I'm migrating into a database. Desipte this these files will stick ...
1 vote
1 answer
111 views

Using Pandas to group data based on name and see if column value is greater than or equal to values based on group names

As you'll see from the below code, I'm creating separate data frames of a much larger data frame, then updating a column for each one. What I'm doing is looking at the second column and checking to ...
2 votes
1 answer
135 views

One way Synchronize 2 Access Tables with VBA ADODB

I am synchronizing two MS-Access databases. One of them on a network drive, the other one on the local machine. (For testing purposes both in the same directory) The Databases both contain a table ...
8 votes
3 answers
2k views

Evaluating formulas greater than 255 characters

Recently I was receiving Error 2015 when trying to use Application.Evaluate, then I realized it was because my formula was greater than 255 characters. As a ...
0 votes
0 answers
62 views

Extracting a substring based on a pattern

As part of a project to pull data from our purchasing system (Sage) back into our quoting system (an Excel template) to automate variations to existing quotes, I wanted to grab the original quote ...
1 vote
1 answer
161 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 ...
1 vote
0 answers
63 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
133 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 ...
3 votes
2 answers
522 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 ...
2 votes
2 answers
197 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 ...
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
128 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 votes
2 answers
70 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 ...
1 vote
0 answers
56 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+...
6 votes
3 answers
1k 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, ...
0 votes
1 answer
88 views

VBA Excel - autosum values from all workbooks in the directory

I have the code such as this for opposite conditions. ...
2 votes
1 answer
241 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 ...
3 votes
1 answer
91 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. ...
1 vote
1 answer
501 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 ...
2 votes
1 answer
79 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 ...
4 votes
1 answer
181 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
599 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 ...
3 votes
1 answer
130 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, ...
3 votes
2 answers
944 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 ...
0 votes
1 answer
62 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 ...
2 votes
1 answer
170 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 ...
2 votes
1 answer
1k 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 ...
4 votes
1 answer
191 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 ...

1
2 3 4 5
23