All Questions
56
questions
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 ...
- 275
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. ...
- 31
4
votes
2
answers
987
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 ...
- 275
6
votes
1
answer
448
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, ...
- 275
0
votes
2
answers
173
views
VBA - Compare & Consolidate Dynamic Arrays
Background
The CSV files being used can be 6+ mill records in size, using a small sample size to proof the methodology.
Why are you doing this on excel? Access or SQL would be quicker
My company has ...
- 11
0
votes
3
answers
91
views
Populate array using elements from four source arrays
Long piece of code, I hope it is readable.
The code is basically if statements looking at 4 arrays: ArrPnLDataD1, ...
- 13
2
votes
1
answer
690
views
=PRINTF("{1} - {2}", ... ) in Excel without VBA - LAMBDA functions with variable number of arguments
The TEXTJOIN function has a really nice interface:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252])
... where ...
- 2,395
2
votes
4
answers
216
views
To what degree is my VBA Array size function safe, robust, and performant
Coming from a Java/Scala background with a strong focus on OOP+FP, I recently started working with Excel+VBA to implement a front-end for a SaaS. It was like stepping way back in time, over two ...
1
vote
1
answer
95
views
Looking for a more condensed method of assigning values by using arrays and fixed ranges
I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.
The Macro is within ...
- 41
2
votes
1
answer
113
views
Output the rounded sum of each column in selection
I've written something (i.e. frankensteined from stack exchange) that appears to work but I haven't done much testing on the edge cases. Signed up here for some feedback on what optimizations or other ...
- 23
3
votes
1
answer
171
views
Array manipulation object
I'm trying to write a class that simplifies the most common operations with arrays and then I want to distribute... Maybe it can help someone...
But I'm facing some problems in make the object ...
- 354
2
votes
1
answer
150
views
OOP approach to filter two-dimensional array
So, after this functional(?) approach and thanks to the great improvement of @CDP1802, I've decided to try an OOP approach to filtering an two-dimensional array.
In my opinion, the result is way more ...
- 354
5
votes
1
answer
420
views
Filter a two dimension array
This is the function I've wrote to filter a two-dimension array.
I use it mainly on forms with user-defined filter (categories, dates, search bar, etc).
It works fine, but it's ugly.
Do you have ...
- 354
5
votes
4
answers
189
views
Check non-continuous range for equal values
Background
I'm trying to check equality between cells in an irregular, non-continuous range of cells. For this purpose I would like to know if all cells are equal to the first ...
- 218
1
vote
1
answer
104
views
Comparing information to a directory of active enployees
The code is doing everything that I need it to. It's taking much too long though upwards of a couple minutes to complete the macro. Does anyone know how I code optimize this VBA code to run quicker? I'...
6
votes
1
answer
388
views
Excel's Dynamic Array Functions...In VBA (No Win 32)
In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
- 668
3
votes
0
answers
2k
views
Alternative for VLOOKUP/Index-Match in VBA
I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-...
- 668
2
votes
0
answers
2k
views
VBA array functions: push, pop, shift, unshift
I want to write my snake game procedurally, using as much windows call as I can so as to practice. Looking into GetAsyncKeyState to capture keyboard inputs and play sound functions. Also making a ...
- 525
3
votes
1
answer
116
views
Array that stores values from two ranges and compares if one cell is blank
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is ...
- 593
2
votes
0
answers
96
views
Use arrays to perform 'VLookUp' type activity between workbooks
Background
I have the below UDF that opens a workbook (this workbook has extracted data from a queue within another app). I get the used range from this workbook and assign it to an array (...
- 121
2
votes
2
answers
235
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 ...
- 131
6
votes
2
answers
112
views
Find select values on one worksheet and copy to a second
In the Calculator worksheet of my spreadsheet, for any appliance type present in a building being studied, users enter a quantity. There are 18 different appliance ...
- 419
3
votes
1
answer
487
views
Find winning move in Tic-Tac-Toe with boolean arrays in VBA
I've been trying to mess around with boolean arrays in VBA since I started to try to write an algorithm to solve sudoku puzzles. I thought I'd first try to solve codeabbey tic-tac-toe. I might be ...
- 7,159
2
votes
2
answers
194
views
Mapping one array onto another where columns from first array become rows in second array
I am processing an unformatted CSV, New-AmbSYS-to-2018-Jan.csv, from NHS England Ambulance Quality Indicators, by reading its contents into an array, and carrying out a number of steps that lead to a ...
- 375
2
votes
2
answers
2k
views
Reorder columns in Array
I am reordering columns in an array and removing one column. The mapping is as follows:
...
- 375
1
vote
2
answers
93
views
Extracting filenames from a URL by splitting a string
I am used to accessing items from an array, returned by the Split function, directly. This is lazy, I know. Usually, I know the element I want and I say something ...
- 375
1
vote
1
answer
333
views
Array Wrapper Class
I’ve wrote a small array wrapper class which mimics the built-in Collection for adding and retrieving items. Once the object is created, you set the array capacity and that would not change.
...
- 215
2
votes
2
answers
235
views
Multiply a 2D array by 1D array to get a third (2D) array
I've working with three dynamic arrays (all are datatype Double) - they are
OriningalArray
This will be assigned from a range that the end user will see and ...
- 131
5
votes
1
answer
90
views
Merging List Arrays
Some utility functions I re-wrote today:
Purpose:
Given an arbitrary list of 1-D arrays (possibly empty variables), return a
1-D array containing unique values from all lists.
Ex: Given (1,...
- 8,800
2
votes
1
answer
148
views
VLookup simulation: copying data from one sheet to another
I tried to write a VLOOKUP in my macro but for some unknown reasons it didn't work (see my post on StackOverFlow for more info.
So I decided to do a macro which achieves the same result, but it takes ...
- 121