Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [excel]

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

0
votes
0answers
31 views

Hiding summary rows in named sheets [on hold]

I have code that currently uses tab names instead of the code names. The problem is the tab names update making the code not work properly. I am looking to change the following to code names instead ...
1
vote
1answer
51 views

User form that searches a spreadsheet

I have this userform that looks up into a sheet. The problem is that when there are a lot of rows to look up it takes too long to execute (for example 100 thousand rows). Is it possible to reduce the ...
2
votes
2answers
77 views

Index Match implementation

The code below takes header variables, retrieves the column index, and then, using an Index/Match function, returns data from a matching account number. The reason it is written this way is because I ...
1
vote
1answer
32 views

Sending data from sheet to other sheet in Excel (from a dispatch note to some records)

I have this code to save data into some records. It is a little slow, so how can I make it faster? To the first record (sheet MOV MERCADERIA) it copies 23 columns ...
3
votes
2answers
95 views

SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class

Going to take a look at Rasta's comment below tonight and just realized I am using byref to pass everything again - DUHOH - Old habits die hard I have revamped with some of Comintern's improvements ...
2
votes
2answers
90 views

Automating the creation of a weekly schedule

I'm trying to build an Excel workbook to automate and aid in the creation of a weekly work schedule. My current workbook is functional, but it's slow - especially on one task where I have a list of ...
7
votes
1answer
181 views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/SpaceInvader/tree/master EDIT: SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class ^ a link to second iteration. ...
8
votes
1answer
116 views

Battleship UI: GameSheet

In the previous post I presented the MVC architecture, but all we saw of the View was the pretty bits. From the outside everything looks innocently pretty: The guts of the worksheet's code-behind is ...
2
votes
0answers
40 views

Excel for travel expenses

Every Month I need to make an excel sheet with travel expenses, since this is a tedious task I decided to make a little python script to do this work for me. Everything works correctly, but any ...
1
vote
1answer
31 views

Copying data from various sources into a master list

I am working on a Master List, where I am copying data from various sources for each month into the columns Z, AC, AF, AI etc. (always separated by 2 columns). Then I copy that cell all the way down ...
1
vote
0answers
37 views

Insert Emojis Into Outlook Email With VBA Application (For those of us who cant use add-ins)

I cannot use add ins here so I set out to create an excel application that could get the job done. I suppose that I could easily transport this into outlook? Basically I want to click one of the ...
1
vote
1answer
31 views

Function to get constants of range

The following small function receives a range and returns it with only the cells that contain constants or Nothing if there's none. ...
1
vote
1answer
74 views

VBA Script to Remove Duplicates

I'm curious if there's any way I can substantially improve its speed (it runs through >200k rows). I think I'll most likely need this once, so please bear with me since it probably looks terrible. ...
0
votes
1answer
38 views

Copying ranges from multiple Excel sheets into a main sheet

I have a VBA code which copies same data from Multiple sheet and then paste it in "Main" Sheet. It then auto fills the blank cells for values from above and then it delete all the rows Where H:H is ...
3
votes
1answer
79 views

Running SQL Queries On A Worksheet (With Set Of Random Numbers Between 1-20 Filling Column A) Handled As An ADO

INTRO I am using an Excel worksheet as my "database." No headers so [F1] is assigned to column one by default. I filled the entire column, all 1,048,576 cells with ...
2
votes
1answer
27 views

Compensation Year-End Planning VBA Script

I have a pre-planning file for year-end review that does a lot of index/match formulas and IF statement formulas based on certain parameters when referencing ...
2
votes
2answers
48 views

Conditional highlighting (Font size colour, size and Bold) using VBA

Note: Question originally asked in SO. Objective of Code In my worksheet I have several columns that need to be compared to other columns. None of these column pairs are contiguous, so it can be ...
0
votes
1answer
53 views

Class Module of Table properties, Record Table HeaderName and Column number to dictionary

This code creates a class module that record headers in to a dictionary which then allows for copy and paste data from another file where by the headers match. This helps to create a more dynamic ...
-1
votes
1answer
9 views

How do I increase my VBA macro speed?

This is my VBA code. It's actually adding COUNTIF value to the next sheet of columns. when I'm running this it will take at least 7 minutes, how to decrease that time? ...
6
votes
1answer
145 views

Sudoku-lite challenge

You can find the problem here. The example: Column A, B, and C are each 3 and together equal 9: ...
2
votes
1answer
59 views

Executing arbitrary SQL statements using VBA in Excel

I built a workbook to help me extract data from a MSSQL database into Excel. I realize that this is fairly easy to do with Microsoft SQL Server Management Studio, but I can't easily get that installed ...
4
votes
2answers
67 views

Macro to convert an indented hierarchy to a database format

Our business intelligence suite outputs data as an indented hierarchy, as below: ...
0
votes
1answer
88 views

Generating output comment for 70000 rows

For traversing 70,000 rows (using a for loop), my macro is taking a lot of time to give output comment as per various conditions (...
3
votes
1answer
87 views

Export to Excel From DB is too slow

My function works pretty slowly with big results from the db (420000 records and growing). Can some one give me any proposal to make it faster? ...
0
votes
1answer
38 views

Pasting from another workbook

I have a macro comprised of this and similar Subs. They work, but I feel like it's slowing it down. Two workbooks are open, and various ranges are copied from one that is hidden (wbSource) and pasted ...
4
votes
2answers
63 views

Importing files into Excel

Any tips to reduce lines, improve speed, or any cool thing, are welcome. I have been using this for years and I just realized, why not improve ...? ...
1
vote
1answer
39 views

Read unstructured Excel payslip and export to structured Excel table

I've not used this site before, I would like to get opinions on the code I've completed for my first 'program' in Python. I would like to understand how 'good' it is and where it can be improved, ...
4
votes
2answers
220 views

Dijkstra's algorithm implemented in Excel VBA

I wanted to implement Dijkstra's Algorithm in an Excel VBA Add-In and built it to be used as follows: Define a list of paths with distances between points. This list needs to contain 3 headings that ...
-1
votes
1answer
43 views

Macro to clear cells that do not appear in a master list

The macro below clears cells that do not match any cell in comparison to a range (a master list). It works on small file but is too slow to work for files with large ranges. ...
0
votes
1answer
78 views

Macro that combines data from multiple worksheets

I have created a macro to combine data from different excel sheets into one final report. As it is obvious from the code provided below, there are very clear steps in the code. The code has no issues (...
1
vote
2answers
120 views

Matching a checkbox caption to a cell's value

There are 30 checkboxes that need to have their caption compared to a cell value. The basic code is repeated 30 times for 30 different check boxes The only Lines that Change per CheckBox are the ...
-1
votes
3answers
125 views

Writing many cells to sheet causes Excel to not respond, replacing vlookups [closed]

I have found that my code causes my Excel to 'not respond' when it gets to this for loop, For thisRow = 2 To lastWSrow(outWS). I'm guessing this is because I am ...
0
votes
0answers
37 views

Collating data from multiple worksheets, conditional

I'm currently creating a 'master' tasks sheet for work, that can draw uncompleted (pending or overdue) tasks from multiple worksheets (upto 10 different projects) and sort through the data. The most ...
1
vote
1answer
54 views

Store account numbers as text

I wrote this code to make sure account numbers (accounting), which are numbers, are stored as text in the range. Replacing r.value by ...
2
votes
1answer
50 views

Getting rid of Merged cells

Merged cells are a PITA in Excel. They prevent doing a lot of actions and make copying columns impossible. So I wrote this little code to change all the merged cells in a given sheet into 'Center ...
4
votes
2answers
184 views

Excel 2016 VBA - Show/Hide multiple shape groups [closed]

I have the following code I'm using to show/hide multiple shape groups in Excel 2016 using VBA and macros: ...
5
votes
2answers
456 views

Hide Excel Rows faster

I've built a VBA Excel macro that works on a spreadsheet of duplicated files, and displays only groups in which at least one duplication is owned by the current user. The spreadsheet lists file ...
0
votes
1answer
77 views

Mapping between 3 sheets, write to final sheet

I have the following code that causes my excel to 'not respond' however if I step through put a breakpoint at the loop ...
-3
votes
1answer
81 views

Calculate quantities in a userform [closed]

This is used on an Excel order form to calculate quantities in a userform for variables based on various criteria on the worksheet. It's too much code for a single Sub, but I'm not sure how I can make ...
6
votes
0answers
88 views

IndexedArray Class: Uses a Dictionary Keys to Index a 2 Dimensional Array of Values

This class encapsulates a 2D Array and a Scripting Dictionary. Values are add or returned from to the Array using a Key and a ColumnIndex, e.g. ...
3
votes
1answer
126 views

Auto filter data from worksheet and create vlookup using a different worksheet

I'm working on a large VBA project for work (around 1000+ lines of vba code) This module is called within another module where I disable everything possible like screen updating, events and setting ...
3
votes
5answers
774 views

Writing an array to a worksheet [closed]

When I run my code, I get an "Excel not responding" error at this sub as I step through the code, more specifically the write portion of this code. Can anyone help ...
0
votes
1answer
122 views

Performing transformations on files [closed]

The code opens some files and does some transformations. I am looking for syntactical help where I can do the same thing I am doing now but speed it up. I can supply some of the calls code if ...
1
vote
2answers
91 views

Retrieve data from eBird API and create multi-level hierarchy of locations

As you may know, I like birds. The Cornell Lab of Ornithology has a tool called eBird where you can submit records of the birds you identified. I recently learned they have an API that I can query, ...
2
votes
0answers
66 views

MS Office Suite VBA: ShellWait() and PowerShell()

I stumbled across this post on SO, and it had a link to some good code; however the code itself was a bit dated. Since the code has a "do not modify" clause I went back to the basics as noted by the ...
4
votes
1answer
207 views

Index Match with multiple columns using input boxes

I find this script handy for preparing ad-hoc reporting. Using two sets of unique identifiers, it allows you to lookup multiple columns of data with the Index Match function. I welcome any feedback or ...
6
votes
0answers
135 views

Bringing The System.Text.StringBuilder Up To Lightning Speed In The VBA

After having to swap the feature rich System.Text.StringBuilder for the very fast but very vanilla A lightning-fast StringBuilder, I decided it was time create a ...
5
votes
0answers
174 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 ...
0
votes
3answers
239 views

Transforming tabular view of data in an Excel sheet into a vertical view

I have the following code that works correctly on small data-sets, but times out and crashes on large data sets. The goal of this code is to take a tabular view of data on one sheet and transform it ...