All Questions
38
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
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
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
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