Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
3
votes
2answers
34 views
Writing an array formula to a large number of cells
I am working on a script to write an array formula to a large number of cells. I have a code that works, but is very slow. Any thoughts on how to speed it up? The end goal is to be able to quickly ...
3
votes
1answer
38 views
Compare two datasets in Excel VBA
I made the following code to take two reports and compares them to show the end user elements which are missing from one of the reports so they can make the adjustments needed.
This is the main ...
3
votes
2answers
39 views
Excel 'Big' Data Processing Followup
Previous Question (Initial Level Optimization) : Excel 'big' data processing with vlookups
Code Purpose: Recalculate 25 columns of 500,000 rows each based on new information (provided by a ...
5
votes
1answer
34 views
Remove known Excel passwords with PowerShell
I have this PowerShell code that loops through Excel files in a specified directory; references a list of known passwords to find the correct one; and then opens, decrypts, and saves that file to a ...
8
votes
3answers
85 views
Perf wrapper for Excel VBA
For a while now, I've been using this wrapper to avoid retyping most of it :
...
6
votes
1answer
102 views
Excel 'big' data processing with vlookups
I was given code a while back that was a mess, and have completely rewritten it. However now the code takes .2 seconds per line which for a 500,000 line document is much too long.
Purpose of the ...
3
votes
1answer
25 views
Minimize the number of workers/test rigs, with a capacity constraint, using Excel VBA Solver loop
I created a code that runs 60 optimizations through solver through 4 series of loops (each with 15 'iterations'). The code works great but it is taking FOREVER to run through (over an hour). Each ...
6
votes
3answers
325 views
Importing multiple worksheets
I use the following code to import multiple worksheets from another workbook and do some processing. The importing time is too long. Can anyone suggest a more efficient way to import? Should I be ...
3
votes
0answers
34 views
Excel VBA: Set the background-color & border for a user-defined range of rows
On my work I've got to maintain an Excel-sheet. Documenting the incident-tickets I've dealt with.
For to be able to distinguish between single days easily I mark every second day with a light cyan-...
3
votes
1answer
44 views
Import Title / Data header Validation Code
I would like some feed back on the following code.
The Idea is as follows,
I have a control spreadsheet which will hold data headers for a variety of reports. The macro will compare the data headers ...
6
votes
0answers
44 views
Error handling to restart Word from Excel inside a loop
I'm often opening, from Excel, all Word documents inside a folder.
And sometimes during the treatments, Word crash without throwing an error.
So I've added a bit of error handling to restart Word ...
1
vote
1answer
42 views
VBA Macro to generate a report of mean values for each date
My Macro that I wrote is running really slowly and I'm not sure why. The Macro deletes the area, then reproduces one column of locations as a row, deleting any duplicates, then copy's across a column ...
3
votes
1answer
47 views
Looping through cells and deleting column
A quick description of what I want:
Loop through a particular cell row and it's columns
If any cell contains 0, then delete the entire column
So let's say 4,4 was 0, then delete that column (D)
...
2
votes
1answer
77 views
Sorting efficiently by multiple columns [closed]
I need to sort a large array (5 columns x ~500,000 rows) of string variables by two columns. I have code that works, but it takes an unacceptably long time, particularly because I have to do this on ...
5
votes
2answers
61 views
Excel to JSON parser with http download
I've been working on a project (link) to download a spreadsheet of known ransomware and properties and turn it into json so I can better consume the information within early detection projects.
I'm ...
6
votes
2answers
47 views
Fixing Medical Claim Files through Text File Read/Write
With thanks to @Mat'sMug and @Comintern for their encouragement, here is a program I wrote to help my team fix medical claim files.
The goal of this program is to make the process of emergency ...
3
votes
2answers
76 views
Applying same formatting to multiple borders in Excel VBA?
Is there a better way to format the cells with borders than what you get when you record a macro? For example, I want to add borders to a cell range. The recorded code is:
...
13
votes
0answers
130 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 ...
6
votes
2answers
47 views
Create table that lists worksheet visibility
Following my previous question Create a table that lists macros in a workbook or worksheet here's my Sub to determine worksheet visibility in a workbook. This arises from updating code that used ...
6
votes
1answer
58 views
Create a table that lists macros in a workbook or worksheet
I'm updating an Excel workbook that I've inherited and had to figure out if/when/where Functions/Subs were being called. Ran into instances where in the code I couldn't find it being called and ...
6
votes
1answer
91 views
Excel VBA Explanation/Optimisation Needed
I am a self taught VBA user. I was asked to look at the code from a tool which has stopped working due to a data overflow and "fix" it. I was told the code was 'optimised' so that it took only 2 hours ...
5
votes
1answer
59 views
Mirror and update cells across tabs and forms
From the requests below I've attempted to rewrite my request. I agree completely that it would be easier for our company to provide us with help desk software. Unfortunately, my department has the ...
3
votes
0answers
38 views
Comb Sort Multi-dimensional Array on Key
Previously: Comb Sort in VBA
I've been running two different comb sorts (on arrays) depending on whether I want to sort numbers or strings and I figured I would give using ...
7
votes
1answer
52 views
Get data from user input as workbook or worksheet
I had two different functions for populating a data array. I tried to refactor them using a single argument as to whether or not the user should be selecting a worksheet or just the workbook.
The ...
3
votes
1answer
52 views
Iterate through slicer settings and print to PDF
I have a spreadsheet with seven tables (tbl_1, tbl_2, ..., tbl_7) each controlled by its own slicer. Each slicer has six buttons ...
4
votes
0answers
38 views
Dynamic creation of check box [closed]
This code helps to create check boxes dynamically, as you can see in the image, but it takes more time to create them. It sometimes lags for 5-10 sec and then it creates the check boxes.
Can this ...
12
votes
4answers
894 views
Delete rows in spreadsheet where cells match some patterns
I have vba code that loops through a large number of records and deletes rows based on criteria. The issue at hand is that it takes far too long to run. I have never actually let it finish because it ...
3
votes
1answer
50 views
Convert BeautifulSoup4 HTML Table to a list of lists, iterating over each Tag elements
I am trying to convert a BeautifulSoup4 HTML Table to a list of lists, iterating over each Tag elements and handling them accordingly.
I have an implementation of this that works at a surface level ...
9
votes
1answer
91 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 ...
4
votes
1answer
45 views
Populating monthly report with new data each month
This code basically checks a cell for the current date and then fills out the report by finding that date and placing the correct values in the correct places. It works fine I just know that I am ...
7
votes
1answer
86 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 ...
5
votes
2answers
60 views
Separating data by a particular cell
I am trying to improve some code I did not write, mostly to make it easier for other people to understand (I found it to be really weirdly written). I attempted to rewrite it, and it does basically ...
6
votes
2answers
390 views
Find cells with specific formatting
I'm currently writing code so that when a red-colored cell appears in a column, a message box will appear letting me know that column has an error. I have written the following method:
...
2
votes
2answers
75 views
Efficient method to sort read-in text file in Excel
I've read in a text file to excel from a database and I've done it in such a way that it filters out unnecessary columns. My approach to filter rows was to use two subroutines and call the 2nd from ...
2
votes
1answer
56 views
Comparing two arrays and printing results as a collection of a class
This is really my first time using a class and I originally wrote it without the class and without refactoring.
This is to take two inputs (csv), place the data in arrays and then compare the arrays. ...
6
votes
1answer
78 views
Detailed Last Row Finder
For a project I created a more detailed last row finder which looks at more than one column.
Could you provide any feed back on the code below.
...
2
votes
2answers
86 views
Search through each file in a directory, list number of instances of searched string
I am trying to open each file listed in Sheet2 Column A, search for a string of text located in Sheet3 Cell B1, list the number of instances this string appears in each particular file in Sheet3, and ...
3
votes
1answer
66 views
Loop through Excel files and copy correct range in a separate file
Intro: Today I have decided to make an Excel automation task with C#. This is probably the first time I am doing something like this, thus the problems are plenty.
The task: Pretty much, the idea is ...
4
votes
1answer
67 views
Merging rows in excel using VBA
For the first time I've worked in Excel VBA to find rows in my dataset that contain the same adress as another entry in a cluster. These entries have to be merged and the row then is deleted. I've ...
1
vote
1answer
44 views
Executing a function's flow
My __main__.py file has a function's flow, based on methods of each class instance, which each one has its own methods and attributes, like this:
...
3
votes
1answer
101 views
Batch convert txt files to xls
In this SO question I provided two answers for the batch conversion of more than 100K txt to xlsx files
I suspect any automation of Excel is too slow, but would appreciate if there is a superior (i....
9
votes
3answers
103 views
Remove unwanted characters from specified column in all Worksheets
I need to remove some unwanted characters from a given column in all Worksheets from my Workbook. The code below works perfectly, but I'd like to know if there is a better/cleaner/faster way of doing ...
2
votes
0answers
72 views
Speed issue when controlling Excel from Access VBA
I have a very hard time figuring out how to proceed. I built an Access DataBase which I use to process data in order to build several weekly and monthly reports. I wrote VBA codes in Excel and in ...
8
votes
2answers
76 views
Worksheet that lists a person's name
I have a worksheet that lists a person's name (column A; many duplicate names are in this column) with associated data (columns B through G). I have code below that takes this list of a ~ 1000 rows ...
4
votes
2answers
72 views
Pairing all identifiers from 3 sheets and list them on a report worksheet
I have this, and I think it's working? It's been running nearly all day and still isn't finished, so I'm hoping there is a better way to code it.
Right now I have a series of loops that iterate ...
5
votes
2answers
50 views
Summary Data According to Machine No, Order No and Shifts
Here is the code that I write for my work on Excel-vba. I use that code to know shifts' and operators' performances.
Main purpose is to divide machines' work to orders and shifts.
...
4
votes
1answer
51 views
Transfer of data instead of copy.paste.clear used
I am using this code in a worksheet to check the condition, and based on the condition, copying the entire row of values in a different range of particular condition if true.
For copying I am ...
2
votes
1answer
65 views
Go to next visible cell without loop
I have an autofiltered table in Excel. I have to copy/paste values based on certain conditions and I also have to perform this on all visible cells in a particular column. I have written the code and ...
3
votes
1answer
118 views
Basic Password System in Excel VBA
I have created a basic login system that asks the user for a password. Here's a list of what it does:
When you run the code it will open an Inputbox asking you for a password.
There is one criteria: ...
0
votes
1answer
47 views
Copy/Paste of Range
I have gone through all steps to optimize the code, deactivating screenupdate, deactivating calculation, events, and pagebreaks removed the unnecessary selects, adding constants, and etc. However, I ...
