Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with excel
Search options answers only
not deleted
user 227582
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
3
votes
Accepted
VBA Excel substitute function for multiple conditions at once
You do not want to read and write the cell multiple times. You only do a read and a write. You also need to check if the cell has a string. There is no point in running the replace on numbers or error …
- 1,596
5
votes
Accepted
Copy a contiguous sub-column of cells
I will address your code in sections and at the end I will provide a full updated code based on the touched subjects.
Application state
You are turning the Application 'off' with:
Application.ScreenU …
- 1,596
1
vote
Transpose and Paste VBA
Consider creating 2 named ranges (via Excel/Formulas/Name Manager) and referring to those instead so that in case sheet names are changed or ranges position/size is changed then the code does not fail …
- 1,596
2
votes
Add padding to top and bottom of cells
back the original values with rng.Value2 = arr
This actually runs 60% faster than your method but unfortunately does not work mainly because when writing back the original values the padding is lost as Excel …
- 1,596
5
votes
Excel VBA code midStr() function using left and right position instead of length used with m...
Moreover, an Excel User Defined Function (UDF) is usually written with UPPERCASE.
This function seems to be designed as an Excel UDF. What if you need to use it in VBA? … The Excel errors mean nothing to another VBA method. …
- 1,596
3
votes
Accepted
Create Table After Deleting Rows Before Desired Range and Filter to Delete All Other Unneces...
Getting the desired worksheet
You are using the GetWorksheetByName function which will either return Nothing or a valid worksheet object:
On Error Resume Next
Set ws = GetWorksheetByName(expectedShtN …
- 1,596
4
votes
Accepted
Loop between two Arrays and change values based on condition
As @Greedo mentioned, it is much simpler to write a formula, be that XLOOKUP or a combination of INDEX and MATCH with fast results and easier maintenance.
However, if you still need to do VBA for what …
- 1,596
2
votes
VBA Very Slow Performance using objects with scripting dictionary
Speed issue
tags.Items does a copy of the array of items. So, you are copying that array 32821 times which of course is slow.
Replace this:
For i = 0 To tags.Count - 1
If tags.Items(i)(2) = 0 Then …
- 1,596
6
votes
Accepted
Fastest function to `Remove Duplicate Lines` per each cell
Binding
Late-binding is slower than early-binding. Add a reference to Microsoft Scripting Runtime:
then change Dim dictionary As Object to Dim dict As Dictionary
and Set dictionary = CreateObject("Sc …
- 1,596
3
votes
Accepted
Sub that calls itself when its parent Workbook is closed
Unfortunately, the Release magic trick is dangerous because it can mess with the underlying EbMode function used by VB to determine if code is running or not.
To replicate, run Main as presented in th …
- 1,596
3
votes
Accepted
Speed up recursively looping through network folder and subfolders in VBA
Get files
You can speed up retrieving the files by importing LibFileTools module from VBA-FileTools. Your recurse method then becomes:
Private Sub recurse(ByVal folderPath As String, ByRef fileDict As …
- 1,596