Questions tagged [excel]

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

Filter by
Sorted by
Tagged with
7
votes
1answer
234 views

VBA Class converter to eliminate strings being used as valid values

I'm trying to avoid strings being passed around as a valid value. The ability to make a mistake is too high for my taste and the solution that I've come up with is to use an ...
7
votes
1answer
1k views

Import Excel, unpivot data, output tab-delimited for SQL Import

I have a client GL file (Excel) that needs to be regularly imported to SQL Server (as tab-delimited txt). Sample (simplified): ...
7
votes
2answers
385 views

Optimizing this Excel automation

The createReport method takes around 30 seconds to execute and I was wondering how I could optimize it. I'm using the ...
7
votes
1answer
201 views

Class: 2D Variant Array

Note: Yes. It's big. I'm not expecting commensurately long/detailed answers (though if anyone wants to write one, you'll definitely be receiving a substantial bounty). This class is going to be used ...
7
votes
1answer
362 views

Python program to transform an Excel document, with some parsing

I wrote this to parse through an Excel doc, extract desired columns, parse certain cells and output another formatted Excel doc. This is the original Excel doc I have condensed most of the columns ...
7
votes
1answer
1k views

Delete rows from ListObject based on one or more criteria in a column while maintaining original sort order

The title is fairly self-explanatory re: my goals, though I'll add that speed/efficiency is a priority. Originally, I tried using an autofilter on the ListObject and deleting all visible rows. But ...
7
votes
0answers
2k views

Responding to Events of Dynamically added Controls

I was asked for an example of a class to wrap dynamically added controls as I suggested in this Answer. Although it is out of the context of a Code Review, I thought that it would make an interesting ...
7
votes
0answers
261 views

Web Battleship -The Unofficial Battleship UI

I've written my own UI for Mathieu Guindon's VBA Battleship (Battleship UI: GameSheet) which uses a webpage in a WebBrowser control for the View. Webform: Userform ...
6
votes
4answers
1k views

A practical example of evenly distributing n lists into a single list

I had previously asked about how to evenly distribute the items in n lists into a single list and was referred to this question. I made a practical example of my solution for this in VBA for Excel, ...
6
votes
4answers
3k views

Copying data from an Excel sheet to Word

This code copies a specific range and a chart from an Excel sheet to Word. This code works fine but it is very slow. I have used delays(Wait) to work properly while ...
6
votes
3answers
280 views

Remove blank entries from row

I wrote a sub to remove the blank entries in a row without shifting the cells around but it seems unnecessarily clunky and I'd like to get some advice on how to improve it. ...
6
votes
2answers
124 views

VBA - Excel add in Convert number to Text currency

No problem yet with the code. Here is a function that can be saved as add in and used as UDF in Microsoft excel to convert a number to text. For example, 4556.45 to ...
6
votes
2answers
7k views

Code to find value into a table by 2 criteria (x,y)

My code finds the value that corresponds to two values in a table. It works fine, but I'd like to know what you might do differently. Table sample: Input: ...
6
votes
3answers
1k views

Test for Array vs Range in my TEXTJOIN UDF

I use the following code in many of my answers on Stack Overflow, to mimic the new TEXTJOIN function available in Office 365 Excel: ...
6
votes
2answers
1k 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: ...
6
votes
3answers
444 views

Improving performance of VBA batch scripts?

I am using a script to open a bunch of Excel files, copying two or more sheets into a new file and saving this new file. It sure beats doing it manually, but I think it could be faster. Here's my ...
6
votes
4answers
275 views

Deleting ranges of rows from a spreadsheet

I'm currently trying to create a macro, to edit a document that is around 280k rows and 12 columns. For the first steps I found a macro to delete the rows I don't want based on certain criteria, but ...
6
votes
2answers
108 views

Find value from other dataset based on 6 variables that must be equal in both datasets

I have two datasets that are both within a worksheet, call them Data and IBES. The code checks whether the 6 variables are the same in each dataset and then writes the value from a specific column to ...
6
votes
3answers
709 views

Copying columns and formats on condition

I have made significant changes in the code and now it takes about 20~40 seconds to process each worksheet. This will conditionally copy column based on the column headers in sheet2 that match to the ...
6
votes
2answers
539 views

VSTO Write Dictionary To Range Extension Method

I was hoping to receive some criticism on this block of code. I wrote it to write the contents of a dictionary to excel range in VSTO. It works well. I am interested to see how people could improve it....
6
votes
2answers
3k views

VBA Excel - Conditional Formatting Colour Grab

This code gets the cell's colour regardless of whether it's set from conditional formatting or not. It currently works on 2010 and unsure about older versions of excel. My question is is there a way ...
6
votes
1answer
783 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 ...
6
votes
2answers
2k views

Retrieving stock prices

It takes around 5-8 seconds for me to retrieve a previously-closed stock price and a dividend rate from US Yahoo! Finance. If I wanted to retrieve 10+ stock prices, it would take me more than a minute ...
6
votes
2answers
2k views

Searching a huge number of strings from Excel to a large number of Text files

I have written this code to get the input string from the spreadsheet and search the string across the text files and state whether or not it is found in the Excel sheet. Scenario: Excel (sheet1): <...
6
votes
1answer
57k views

Excel VBA - Get Folder Names

I need to get folder names with the path for which I need to search the directory in a server with about 6000 folders. I have the following snippet of code to run through the folder and get the folder ...
6
votes
2answers
3k views

Checking for missing values in several columns of a spreadsheet

Basically I had 3 columns of Data Adjudicated Cases Not Adjudicated Cases All Cases and I wanted to get a list of all the cases that were missing from Column A and Column B, with a little help from ...
6
votes
2answers
1k views

Fast edit of Excel file

I have this code to edit certain cells in my Excel file using Apache POI, but it is really slow. How can I improved the performance? Ideally I would like to edit 20000 rows in less than one minute. ...
6
votes
1answer
2k views

Error-Handling Class and Logging for VBA

I've been reusing an error class in Excel VBA projects for a few years now and I'd like to see if there are ways to improve it. Any suggestions for style, code, etc. are all welcome. The 2 ...
6
votes
3answers
1k views

Split large file into smaller files, keeping headers and footers

I have a folder of SQL files which I need to loop through and: Determine if any of the files are over 20MB If so, split them into multiple files, max 20MB filesize However, there's a complication ...
6
votes
4answers
3k 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 ...
6
votes
2answers
396 views

VBA lookup to complete matrix

I have written the following the code to complete a matrix based on data provided in a second worksheet, but the code is really slow (note that s1=12,000, s2=40 and s3 = 200,000). Any suggestions on ...
6
votes
2answers
233 views

Find and copy pasting optimisation

I am trying to apply this simple macro to merge two big sized sheets (around 30000 rows each), but the process is too slow and never ends. The macro works perfectly with smaller sheets. Could you give ...
6
votes
3answers
509 views

Parsing excel cells containing line feed characters

This is a direct follow up to Parsing cells containing Line Feed Characters. Link to sanitized xls on dropbox if test data is needed Essentially the reports I work with aren't bad - The issue is ...
6
votes
1answer
418 views

Non-standardised data across multiple worksheets: Aggregation, Validation, Filtering

Every piece of business that gets written (E.G. an adviser's client puts an additional £10k payment into a pension plan) goes in a spreadsheet called the SubSheet. It's split into 8 worksheets for ...
6
votes
2answers
9k views

Copying from sheet to sheet if column headings match

Basically, I have three sheets in my workbook where only the second and third worksheets are being used. I want to copy columns from the second sheet to the third sheet only if the column headings ...
6
votes
2answers
2k views

Speeding up Excel distance calculation using Bing API calls

I am writing VBA in Excel to calculate the distance between an employee's home address and work address using Bing Maps API calls. The process follows this general flow: 1) Convert the employee's ...
6
votes
2answers
110 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 ...
6
votes
2answers
5k views

PowerShell script to create Excel spreadsheet from HTML file

This script is designed to create an Excel spreadsheet from each HTML table from auto-generated index.html file. It needs to be very robust but unfortunately I ...
6
votes
1answer
980 views

Print worksheets depending on checkbox. Slower than before

I have an userform that has check boxes where user can decide which worksheets to print. Code in userform: ...
6
votes
2answers
107 views

Comparing vehicle crash data the previous year

I have created an automated macro which takes vehicle crash data from a .csv file and automatically creates a pivot table and charts and compares it to the previous year. The code is approximately ...
6
votes
1answer
452 views

Looking up ingredients and creating a shopping list

This is my first attempt at refactoring - FindIngredients and I'm wondering where I can improve. The github link in the code directs to the project that still has ...
6
votes
2answers
285 views

Deca dimensional ascending and descending array sort function

Some folks told that a two-dimensional array can solve almost all the problems, but let me disagree with that. That's why I made this Deca dimensional array sort function. The array must be ...
6
votes
2answers
1k views

Copy data to another worksheet

I have the following but it takes long time to run, it is a simple wherein the user select a file and data from Sheet1 is copied to another workbook. ...
6
votes
2answers
176 views

More efficient update macro in Excel

I have a macro that makes comparisons and then this macro exports all of the changes based on if the information doesn't match. I have it so that each column gets their own worksheet in the new ...
6
votes
2answers
46k views

Excel's SUMIFS implemented using PANDAS, the Python Data Analysis Library

I've implemented Excel's SUMIFS function in Pandas using the following code. Is there a better — more Pythonic — implementation? ...
6
votes
1answer
167 views

Add Default Rubberduck VBA Folder Annotation to VBProject.VBComponents

Currently Rubberduck VBA files all the VBProject VBComponents in that do not have a Folder Annotation in a Folder named after the VBProject. It can be time consuming to manually organize the ...
6
votes
1answer
152 views

Copying strings from files into a new Excel sheet

My code here loops through my folder and my files inside the folder and use .find and copy the strings into a new Excel sheet and then with all the data in the new ...
6
votes
3answers
1k views

Normalizing data in Excel table column with multiple values

I have a table in excel which has 11 columns and 50,000 rows of data. One of the columns contains email addresses and in some instances multiple addresses, separated with a ...
6
votes
2answers
74 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
2answers
2k views

Binary search a 2D array for multiple matching criteria fields

Here is what I came up with for a binary search of an array. It allows for searching one or many columns to match. It assumes that array being searched is sorted with the same sort priorities as the ...

1 2 3
4
5
22