Hot answers tagged

6

Code Style Avoid multiple variable declarations in a single line, like Dim MARCLastRow As Long, FTLastRow As Long, x As Long. It is generally not recommended to include the variable type in the variable name (e.g. ws in MARCws), as variable names should be self-explanatory / self-documenting. Although I know this and don't do this in any other language, I ...


3

TL;DR Instead of looping through each pizzeria and merging temporary subframes, it's more idiomatic to manipulate/map the columns at once. The bulk of the work is just changing the original columns (left) to be filled/dropped/sorted/renamed (right): FROM THESE TO THESE |----------------------|------------...


3

Missing classes You have failed to include your definition of the User class in the code snippet so I had to create my own. class User { public string GameCode { get; set; } public int Count { get; set; } public string Email { get; set; } public int Status { get; set; } } For ConfigurationManager there are 3 different packages that I could ...


3

DRY There is an important software principle called "Don't Repeat Yourself" (DRY). Writing software with frequent use of Copy-Paste is the antithesis of DRY. Not only does it cause repeated expressions with minor differences per copy - it results in code that is difficult to change, debug, and maintain. The purpose of the code is lost in all the ...


2

Each line within the for loop executes a VLOOKUP even though failure by the first VLOOKUP statement guarantees that each subsequent statement will also fail. It would be more efficient to check for 'success' only once per loop and then grab the remaining data after that. So, below is an option that uses the MATCH function within a helper function. The ...


1

A little observation: When many variables are declared in one line it must be specified the type of each one. There are two lines in your code that doesn't accomplish this. Dim strFile, tarFile As String Dim ThisCell, ThatCell, WhichCell As Range The correct is: Dim strFile As String, tarFile As String Dim ThisCell As Range, ThatCell As Range, WhichCell As ...


1

Your question lacks clarity but going by your title the code below will transfer 125 rows from the bottom of the source sheet (less if there aren't as many rows) to a destination sheet. Sub GetRows() ' 310 Const StartRow As Long = 17 Const NumRows As Long = 125 ' number of rows from the end Dim WbS As Workbook ...


1

I don't know if this will work for your particular project, but my first instinct would be to create some hidden rows with values like the following Row 1: Flags to specify if the value should be copied Row 2: Flags to specify if the formula should be copied Rather than hardcoding which values and formulas should be copied, your script can read these rows ...


1

I managed to solve a similar performance problem when I had a Workbook with 5 userforms and a maximum of 3000 rows in one of the 23 spreadsheets. The average in the rest of the spreadsheets was below 1000 rows and a maximum of 10000 lines of VBA code. I was using OneDrive like you, but the problem seems to be the synchronization with the cloud that OneDrive ...


1

The problem I encountered turned out to be unrelated to the code. Instead, interruption was caused by Excel's AutoSave function, possibly in tandem with a weak Internet connection. At this time I still don't know if simply turning off AutoSave would cure the problem. What I have done successfully is to remove my workbook from OneDrive, which disables ...


Only top voted, non community-wiki answers of a minimum length are eligible