I remember I yoinked a macro from this subreddit that automatically removes highlighted merged cells and replaces it with center across selection in one macro. I put it in my personal.xlsb and added a shortcut on my custom macros ribbon so I can do it with one click.
Dunno who to credit but here is the code:
Sub ConvertMergedCellsToCenterAcross()
Dim c As Range
Dim mergedRange As Range
'Check active sheet is a worksheet
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
'Loop through all cells in Used range
For Each c In ActiveSheet.UsedRange
'If merged and single row
If c.MergeCells = True And c.MergeArea.Rows.Count = 1 Then
'Set variable for the merged range
Set mergedRange = c.MergeArea
'Unmerge the cell and apply Centre Across Selection
mergedRange.UnMerge
mergedRange.HorizontalAlignment = xlCenterAcrossSelection
End If
Next
End Sub
Merged cells are a pain in the ass to work with.
Center across selection makes selecting data much more painless but still keeps the aesthetic of centering headings.
Here is a video example of what I'm talking about: https://www.youtube.com/shorts/nw43T1k7uI4. This will make much more sense. The video shows the manual way of undoing merge and replacing it with center across, it's the same thing as what the macro does.
I have made this macro before too but so seldomly needed it I often forgot and usually did it the manual way Select All Sheets > Zoom: 100% click on A1 (I think that set the cursor position for all sheets IRCC)
Here is the macro to do this for anyone interested.
Auto sets all sheets at 100% zoom in your workbook and places the selected cell at A1 for every sheet as well, then goes to the first sheet after it's done.
Sub SetZoomA1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.Zoom = 100
Range("A1:A1").Select
Next
Application.ScreenUpdating = True
Sheets(1).Select
End Sub
I just had Claude work this up for me.
Sub SetZoomAndCursorPosition()
Dim ws As Worksheet
' Loop through all worksheets in the active workbook
For Each ws In ActiveWorkbook.Worksheets
' Activate the current worksheet
ws.Activate
' Set the zoom level to 100%
ActiveWindow.Zoom = 100
' Move the cursor to cell A1
Range("A1").Select Next ws
' Activate the first worksheet after the loop
ActiveWorkbook.Worksheets(1).Activate
End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This was the first reason for me to start looking into macro's. Still haven't had the time to understand and implement them yet though. Would appreciate it if you could share the code!
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I wrote a macro that reads a table into one or more collections (I know there is dictionaries but I don't need additional references for collections) so I can use the values on the worksheet as parameters instead of hard-coding parameters. This has the effect of not needing to edit code unless the algorithm has an issue.
I do a more basic version of that with a “control sheet” where users can update parameters according to their needs; but I’m just using named ranges > string variable usually. Table to collection is a Ferrari to my Honda
The point of using a collection is that when you read the table in you can put the keys into the collection so your variable becomes for example `srcshtcfg("Row Number")` for "Row Number" under `srcshtcfg` and your codes practically write themselves. I got this idea from those .ini files you see from practically everywhere.
Yes I just whipped this up for the first time to automate moving data from an RFQ log to a quote template and creating a file folder for that template in a directory, which also contains subfolders. The location of the template, the mapping of value X in log to sheet and range Y on the template, the directory for the new folder, the naming convention for the template copy and the folder, all the subfolders we want, are all listed in tables which load into variables or arrays.
This way the user can modify the template and change the directory as time goes on, and just change the tables in the Setup sheet of the log, on and they don’t need me to change the code.
Yep, same. I shove an SAP report into Excel and it grabs data from excel and access to email out routine pdf reports to customers. 3 mins saves 2 hours.
So what T-Codes are you using this for? I’m just curious how you’re doing this. Every other day I’m getting an email for someone wanting me to send MB51 transactions to our Sales and Supply Chain colleges who have access to the same damn source. It would be nice to automate this.
Back when I was doing SAP auditing I automated pulling t-codes with the SAP GUI Scripting. Then built macros to pull the exported data into an excel sheet and start baselining the data for my review.
This is something I’ve been meaning to do for a report I refresh and email bits of 2x daily. The annoying part is offloading it to coworkers when I’m on PTO, they end up skipping more complicated steps to the detriment of production.
So you can get it to create an email “template” on the desktop that can just be opened and hit send?
I was hired to do simple data analytics with excel right after college. With the combination of VBA, Python, Bash and SQL I automated my job and got passive income for almost 3 years.
If it’s anything like my job in college, eventually the company finds a better paid developer intern (also in college) to make their database do the same thing automatically. The district managers will complain they don’t get the “raw data” but eventually come around and then I had to find a new job.
Actively working on doing this. Just started 6 months ago and have almost got it working the way I need it to. My job requires matching tables of pc hardware inventory and screws that are different measurements. It’s rather messy data that makes fuzzy matching work inconsistently.
I have one to clear all filters in a table. It's stupid that that needs a macro and isn't baked into Excel!
I also have one that squirts an XLOOKUP into a cell (the parameters of the XLOOKUP are derived from another cell that will be in a fixed position relative to the cell from which the macro is called) then replaces the formula with the value. That's quite a specific use case, but I use it a lot. (Yes, it's a spreadsheet that should really be a database.)
Perhaps look into `Worksheet.ShowAllData` in the object browser. Something along the lines of
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
Endif
Sub Update_tables()
' Update_tables Macro
Dim tbl As Table
For Each tbl In ActiveDocument.Tables
tbl.Style = "Grid Table 1 Light"
tbl.AutoFitBehavior wdAutoFitContent
tbl.AutoFitBehavior wdAutoFitWindow
tbl.Rows.HeightRule = wdRowHeightAuto
Next tbl
End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I balance a GL and have to essentially add up everything that goes through it and add it to this other tracking sheet as debits and credits.
A lot of the same stuff goes through it every single day and is labeled the same thing, so I have a macro that does a SUMIF for all those regular things and throws it at the bottom of the data for me to copy/paste where I need it on the other GL tracking sheet.
Then, I have to work another page in that same process that's essentially just collecting numbers that are always in the same cells. Macro grabs all those numbers and puts them into a nice order for me to once again copy them over to my GL tracking sheet.
Idk if it's my favorite, but it's definitely one of my most used.
And once again I am coming here to tell you about the ways of LibFileTools! Lol
"getlocalpath" changed everything for me.
We are using SharePoint/ OneDrive and I basically export and import everything 100% automated with ADO DB, so getting a local path that is auto generated for every user was a must. I will die the day the tool isnt getting updates and out of service (there are a lot of SharePoint/ OneDrive updates, so you have to update the module often).
[LibFileTools ](https://github.com/cristianbuse/VBA-FileTools)
The problem is that the SharePoint on the local drive is located within the users directory... So the path can't be hardcoded if the file is used by more than one user. I would be surprised if your approach works, haven't tested it tho
Users / Ewgrooss / OneDrive / Files
I usually just use it to find file locations for files people link me. Which means you only need to look at a few of the pieces in the file path to learn the location. The link would never work for a different user.
There was a table of data we used on a website with no export function. I wrote a macro to load the table in an invisible internet explorer window, then loop through the table and put it into an excel report that then created an email in outlook with a synopsis of the data and the full table data worksheet as an attachment. Since it was a report management wanted hourly it saved nearly half of an FTE and minimized mistakes. I was really proud of it.
Was the table behind a login? We have two portals that I could make use of scraping daily or on command but they’re behind logins and I’m not sure if that can be handled.
Its been a few years, but I believe the login was tied to the windows user account so I didnt need to solve for it. Id imagine you could still do it with a login, if you didnt feel comfortable hard coding the login info maybe you could set the ie instance to visible and log in first before the data was grabbed by the script.
Resetting UsedRange is by far my most frequently used macro. It's absurd how often I'll find colleagues made workbooks that have UsedRange extend out to column XFD with data only to like column AA. Or tables that encompass the whole column instead of just the 200 rows used. Quick way to reduce file size by ~2MB :))
```
Sub UsedRange_Reset()
Application.ScreenUpdating = False
With ActiveSheet
uRLastCol = .UsedRange.Columns.Count
uRLastRow = .UsedRange.Rows.Count
For r = uRLastRow to 1 Step -1
If Application.WorksheetFunction.CountA(.Rows(r)) <> 0 Then
If r <> uRLastRow Then .Range(.Rows(r+1), .Rows(uRLastRow)).EntireRow.Delete
Exit For
End If
Next
For c = uRLastCol to 1 Step -1
If Application.WorksheetFunction.CountA(.Columns(c)) <> 0 Then
If c <> uRLastCol Then .Range(.Columns(c+1), .Columns(uRLastCol)).EntireColumn.Delete
Exit For
End If
Next
Application.ScreenUpdating = True
uRLastCol = .UsedRange.Columns.Count
uRLastRow = .UsedRange.Rows.Count
End With
End Sub
```
Have to add a disclaimer though, this code assumes used columns and rows have values attached. Meaning any purely decorative columns/rows at the edges of your values will be deleted.
I just use a formula to define a predictable file name instead. I don't have enough time of my day to Click things. For troubleshooting I can always set the dates or change the parameters which generated the filename.
Agree for output files, I also use formulas, usually with the current date at the end.
Many of my vba use cases have been to take an input file(s), qc/manipulate/append info, and generate an output file for the next step in a process (I work in fundraising).
For the first step, I find file dialog boxes to be intuitive for my users.
You might get some interesting answers at r/VBA.
I'm not sure about "favourite" but I get a lot of repeat use out of my [Dictionary](https://github.com/sslinky/vba-extendeddictionary) and [List](https://github.com/sslinky/vba-list) objects.
Not as fancy as others but I have one that formats numbers to accounting with no decimal places.
It’s simple but being able to press CTRL+shift+K for it is a nice QoL upgrade
it paints the rows of selected range as if it is a table. i like the alternating colors of the tables but i don't want to use them allways.
Public Sub TablePaint()
If Selection.SpecialCells(xlCellTypeVisible).Cells(1, 1).Interior.Color = 16777215 Then 'empty
Call Application.Dialogs(xlDialogEditColor).Show(1, 226, 239, 218)
Else
With Selection.SpecialCells(xlCellTypeVisible).Cells(1, 1).Interior
Call Application.Dialogs(xlDialogEditColor).Show(1, _
.Color Mod 256, (.Color \ 256) Mod 256, .Color \ 65536)
End With
End If
Dim anArea As Variant
Dim aRow As Range
Dim i As Integer
For Each anArea In Selection.Areas
i = 0
For Each aRow In anArea.SpecialCells(xlCellTypeVisible).Rows
i = i + 1
If i Mod 2 = 1 Then
aRow.Interior.Color = Application.ActiveWorkbook.Colors(1)
Else
'Second Rows
End If
Next aRow
Next anArea
End Sub
I use something similar, but a lot of my data has paired rows through a a common identifier. So when I run it, it asks what column contains the identifier (it's different in a lot of cases) and then uses that to only paint the row if it's different from the identifier on the previous row. Makes it a LOT slower than yours, but I love the color selection you use, might incorporate that into mine!
i use similar, however i prompt the user to select the alternating colors they like to use.
Dim color1 As Long, color2 As Long
Dim lcolor As Long
' Check if a range is selected
If TypeName(Selection) <> "Range" Then
MsgBox "Please select a range first."
Exit Sub
End If
' Use the currently selected range
Set rngSelect = Selection
' Prompt user to select first color
MsgBox "Please select the first color."
If Application.Dialogs(xlDialogEditColor).Show(12) = True Then
' User pressed OK
color1 = ActiveWorkbook.Colors(12)
Else
' User pressed Cancel
color1 = xlNone
End If
' Prompt user to select second color
MsgBox "Please select the second color."
If Application.Dialogs(xlDialogEditColor).Show(12) = True Then
' User pressed OK
color2 = ActiveWorkbook.Colors(12)
Else
' User pressed Cancel
color2 = xlNone
End If
i = 1
' Loop through each row in the selected range
For i = 1 To rngSelect.Rows.Count
' Alternate colors for odd and even rows
If i Mod 2 = 0 Then
rngSelect.Rows(i).Interior.Color = color2
Else
rngSelect.Rows(i).Interior.Color = color1
End If
Next i
Sub DeleteRows()
Dim x As Long
With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next
End With
End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Wrote a ConcatSelected that when a range of cells are selected, then macro button pressed, a user form shows with a textbox containing all the cells joined like 'a','b','c' so I can copy paste the csv list into a SQL in() clause
I have a couple of month-end reports that need to be updated with data dumps from a consolidation software we use with an Excel add-in. I wrote a macro to clear the existing pasted data dumps from the report, open and refresh each of the three data dump Excel files from the add-in, paste that data back into the report, and update month headers and column references for the new month end. Usually takes an hour to update everything manually. With the macro, it shaves off maybe 15 minutes, but more importantly I can fuck off and do something more productive while it does the boring stuff for me.
I used to calculate commissions for a couple dozen sales reps. The sales data came from SQL via PowerPivot into a pivot table, but had to be reformatted and cleaned up to the style that the sales team wanted the reps to see their sales. Also had to create the file for the payroll department to load into the payroll software so that the reps could actually be paid and put all of the commissions information into an email to send off for approval. Total process took about 3 hours every other week. My macro saved more time on this one, shaved it down to a 5 minute process with the click of a button.
Perhaps a lesser known aspect of Excel is that this is built in, you can right-click on the arrows to the right of the first sheet.
https://preview.redd.it/zgrnyogxuixc1.png?width=503&format=png&auto=webp&s=3cb576801ed6dced9475d9d7bf029c399870ee08
Yeah no, I assigned the macro to ctrl + shift + x, it populates a window with all the sheets in my workbook, then I can use my keyboard to select which sheet I want to jump to. No using my mouse and it’s 20x faster if my workbook has a lot of sheets.
Edit* actually it seems like we’re talking about the same thing, except my macro means no clicking.
A significant portion of my job involves sending regular reports to other areas of my company that need to be updated with new data every month, quarter, etc. The actual files don't need to be changed much other than for the new data.
Before I took over these reports, the way these updates were handled varied from file to file. Some would require a lot of maintenance to adjust formulas for larger datasets, copy-pasting data from database output, making new sheets based on a template.
Now, I've got a pretty standardized pipeline of how these reports are updated. Data is queried through a data connection directly into the files, and formulas are set up to use dynamic array calculation or table calculation to populate with new data. All that was needed after that were macros to update each report from one instance to the next. And finally, I have a wrapper file that will open each report I list, run the update macro in each, close the file, and move on to the next.
So I have a chain of macros that, with a click of one button, will update each report I desire.
I've got several I use in almost every sheet.
Function splittext(ByVal SplitThis As String, ByVal SplitPoint As String, ByVal index As Integer) As String
Dim LArray() As String
Dim ArrayLen As Integer
LArray = Split(SplitThis, SplitPoint)
ArrayLen = UBound(LArray) - LBound(LArray)
If index > ArrayLen Then
splittext = ""
Else
splittext = LArray(index - 1)
End If
End Function
Splits the text string at each space and returns the second text group, which would be "bbb"
Function revfind(ByVal FindThis As String, ByVal InThat As String, Optional ByVal InstanceNum As Integer) As Integer
Dim NumFound As Integer
Dim That As String
Dim CompareStr As String
Dim Answer As Integer
FindLen = Len(FindThis)
ThatLen = Len(InThat)
If InstanceNum = 0 Then
InstanceNum = 1
End If
That = InThat
Do While ThatLen > 0
CompareStr = Right(That, FindLen)
If FindThis = CompareStr Then
NumFound = NumFound + 1
End If
If NumFound = InstanceNum Then
revfind = ThatLen
ThatLen = 0
Else
ThatLen = ThatLen - 1
That = Left(That, ThatLen)
End If
Loop
End Function
revfind works just like find, but it works right to left instead of left to right.
Sub sheeton()
If Application.Calculation <> xlAutomatic Then
Application.Calculation = xlAutomatic
End If
If False = Application.ScreenUpdating Then
Application.ScreenUpdating = True
End If
If False = Application.EnableEvents Then
Application.EnableEvents = True
End If
End Sub
Sub sheetoff()
If Application.Calculation <> xlManual Then
Application.Calculation = xlManual
End If
If True = Application.ScreenUpdating Then
Application.ScreenUpdating = False
End If
If True = Application.EnableEvents Then
Application.EnableEvents = False
End If
End Sub
These two turn off most automatic calculations that a worksheet does. You use these by running SheetOff before you run a long macro of your own and then running SheetOn at the end of your Macro. This allows your Macro to run at a higher speed and only recalculates anything and updates the screen at the end of your Macro's run, which can speed up long macros.
Sub HideRows(ThisSheet As String)
Dim ColToRead as Integer
Dim FirstRow as Integer
Dim LastRow as Integer
ColToRead = 1
FirstRow = 3
LastRow = 4000
Call sheetoff
For HiddenRow = FirstRow To LastRow
RowValue = Worksheets(ThisSheet).Cells(HiddenRow, ColToRead).Value
If False = RowValue Then
'If row is supposed to be Hidden
If False = Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden Then
Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden = True
End If
Else
If True = Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden Then
Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden = False
End If
End If
Next HiddenRow
Call sheeton
End Sub
This reads the value in column A (ColToRead = 1) on rows 3 to 4000 (FirstRow and LastRow). If True the entire row is set to be visible. If false the row is set to be hidden. Passes in the name of the worksheet as a check.
If called from a Worksheet\_Change(ByVal Target As Range) you can have the sheet dynamically hide and show rows as information changes in the sheet.
Macro that unhides all worksheets:
`Sub Unhide_All_Worksheets()`
`'Unhide all worksheets in this workbook.`
`Dim ws As Worksheet`
`For Each ws In ActiveWorkbook.Worksheets`
`ws.Visible = xlSheetVisible`
`Next ws`
`End Sub`
I've recently made one that's for converting OCR text form a .pdf where it dumps everything in column A instead of the origin table format. You just type in the number of columns and run line by line. You could easily set it to do the whole file, but in my OCR experience, there are specs and spots that will wind up as an extra value of . or , in a row and mess up the offset.
Sub LoopShift()
Dim n As Integer
Dim s As Integer
Dim t As Integer
Dim m As String
Dim o As Integer
Dim sCell As Range
Dim tCell As Range
'n is the number ofcells down to shift over (ex. data range A1:A7 = 6 cells that need to be shifted)
n = 6
For s = 1 To n
t = 1 - s
'Move cells
Set sCell = ActiveCell.Offset(1, 0)
Set tCell = ActiveCell.Offset(t, s)
sCell.Activate
Selection.Cut Destination:=tCell
Next s
'Delete blank rows
m = 1 & ":" & n
o = 1 - n
ActiveCell.Offset(o, 0).Rows(m).EntireRow.Select
ActiveCell.Activate
Selection.Delete Shift:=xlUp
ActiveCell.Select
End Sub
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[DB](/r/Excel/comments/1cgd7qv/stub/l1vcfie "Last usage")|[Returns the depreciation of an asset for a specified period by using the fixed-declining balance method](https://support.microsoft.com/en-us/office/db-function-354e7d28-5f93-4ff1-8a52-eb4ee549d9d7)|
|[SUMIF](/r/Excel/comments/1cgd7qv/stub/l1vibto "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[TEXTJOIN](/r/Excel/comments/1cgd7qv/stub/l1zam3x "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)|
|[XLOOKUP](/r/Excel/comments/1cgd7qv/stub/l1v2ny5 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cnps2z)^( has 18 acronyms.)
^([Thread #33030 for this sub, first seen 30th Apr 2024, 01:27])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I deal with a spreadsheet with ~70 columns and I only need a dozen. I have a macro that hides what I don't want to see, adds a hyperlink, color codes some column values based on other columns or date ranges. I color code my customers to stand out and for filtering. Column width adjustments and a few other things. I use it every day.
I have a list of ink colors with their approximate hexadecimal color code. I made a macro that colors the cell according to the hex code it contains, and also converts it to HSV in the next three columns so I can sort by hue.
Select only visible within a range.
So when you have a pin autofilter on some data, you can select and format only those that haven’t been filtered out
You can also use Alt+; (semicolon) to select visible cells only within a highlighted range. Big game changer when I found out this shortcut embarrassingly recently
My favourite one is one I use for work: it takes data copies from a notepad file (tab delimited), parses it, displays it all in a table format, then cross references that with another sheet (in the same book) which has a list of tickets and displays all of the ticket data on our system (like open/closed, assigned to, etc), then turns it into an actual table, and then displays a bunch of stats at the bottom of the table and makes sure to name all of the cells with stats in them. Lastly, it then updates a few other separate sheets with weekly and monthly stats.
Before I got there, they were doing all of this *manually*. It took them hours, if not most of the day, and it was a collaborative team effort. Now it takes 5 minutes a day!
Does anyone have one that turns a data input separated by commas into separate cells? It's a daily part of my workload at my new job (importing from SQL) and requires fiddling with the menus every time. I tried recording a macro for it but it didn't restore work.
This should work (I used the macro recorder so some parameters might be unnecessary) it assumes you have the data selected and that you want the results to start in cell A1 which will overwrite any data already in there without the ability to Ctrl+Z/Undo:
Sub commaDelimitedToCols()
'Takes current selection of comma-delimited data and converts it to columns
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Semicolon:=False, Tab:=False, Space:=False, _
Other:= False, Comma:=True, TrailingMinusNumbers:=True
End Sub
I have a combination of three navigational macros. The first lists the names of all the sheets on a Home Page, the second jumps to the name of the sheet listed in the cell, the third back to the home page. So, if you are working though a massive workbook you can use it to easily jump from one 'end' of the spreadsheet to the other. You can even just lay down a 'launch pad' by typing in the name of a sheet on the existing tab you are to jump straight to that one.
PS Yes, I know I could use hyperlinks but who has time to use the mouse?!?
I use a bunch of them. Every workday, I download ten .csv tables of data and I need to organize them. So I have a macro that opens each table, pastes it into a worksheet that also applies the date. I have a macro that will delete hidden rows. I have one on a button on my Quick Access Toolbar to apply custom formatting such as when I want +0.0%/\[Red\]-0.0%/"flat". One of my faves is a very simple macro that replaces every 0 digit with a 0, 1 with a 1,... 9 with 9, effectively converting numbers stored as text to active numbers without having to use a helper column.
I probably use pasting a selection as text the most.
If you have a bunch of part/serial numbers, Excel wants them as numbers. And, even if you change the formatting from General -> Text, Excel doesn't change the actual data unless it's re-entered. So, I have a macro that goes through the selected range and does that for me.
For macros, it's mostly about setting up sheets in a form that works, like I give it a chemical composition and it will set up a flash calculation.
For functions, The most helpful for me are ones that reference tables of data. Say, for example, I need a molecular weight for a chemical, I keep all the physical properties in a table in the PERSONAL excel file. That file opens at startup, so the table should always be available. All I need is =mw("dat chemical").
Obviously the code is like a simple vlookup, but I don't have to recall the name of the table, the columns to reference, etc.
Other helpful ones are those that will look up physical property data needed for a calc. If I need density for water at 25 deg C, I can just call my function, it'll do the calc based on the tabulated constants.
It's funny how people will see these things and think it's absolute wizardry. Nope, just a table and 3 lines of code.
Mine would be one that auto saves a pdf copy when I save a spreadsheet if I could ever get the damn thing to work. Everyone I’ve asked says the code looks right but it won’t work
I have been using Excel to do Worldbuilding for a project for quite a while.
My most frequently used macro is one that launches a very complex chain of UserForms. Based on if i use Ctrl+g (Auto-Complete) or Ctrl+Shift+g (Manual-Complete); it goes through launching around 20 unique UserForms which are each designed to do a specific task.
Depending on the size and complexity of the initial state, it can take anywhere from 5 to 30 seconds to fully run. It is not super optimized, but it doesn't really need to be.
All in, I Probably have upwards of >1000 hours of work into it.
#UserForms
UserForms are customizable Prompt Windows. You add them the same way you can add a new Module and they basically do two things.
First, Sub Initialize runs, and you can use that to call whatever functions you want to populate the various elements.
Second, it enters a waiting loop, which is basically just listening for clicks, key-presses, and other 'Events'. As an example, if you add a text box, it will call Textbox1.Click(). If you Type into Textbox1, then Textbox1.Change() is called. You can modify what happens when the elements are interacted with in about any way as you can conceive in programming.
Another example could be clicking a button. Lots of things you can do there too. I usually add a Frame (basically a 'Container Window') with 'Accept' & 'Cancel' buttons.
By default, every UserForm has an 'X' button in the top right, which calls .QuearyClose(). It is also triggered when you use the 'Unload Me' command in your programming. I usually add that to any actions that I want to destroy the UserForm Object from memory.
Most of the time, you don't really want to destroy the object unless you are done with it, but for my specific use case, it would be a lot more difficult to get the behaviors I want from a window that stays loaded.
Assuming you have a Module to write Macros in, you must add a Sub to create, manage, and destroy your UserForm. They can all be in the same Module, or in different ones, as you see fit.
As an example, in Module1:
`Sub ActivateUserForm1()
Dim UF1 As UserForm1
Set UF1 = New UserForm1
UF1.Show
End Sub`
(If you don't use 'Unload Me' in your code somewhere, you can use 'Unload UserForm1' after UF1.Show, and it will be called after QuearyClose is called.)
Now, you can bind that to a command key in the Macros Window > select your macro > options, or bind it to a Shape you can use as a Button by right clicking it and selecting 'Assign Macro'.
#Project
As for the project itself, it is a Procedural Generator for assets used in my DnD campaign. It builds the Skeleton of all the places for me so that I can have a randomized and varied scaffolding to start with.
You can see r/Avastworldbuilding for more information.
I just did a quick count, and I have 30 Sheets of 'purposeful information' so that doesn't include the directory, examples, templates, etc..
My main sheet, Autogen, is down to Row 10,396.
I have 116 individual Assets, about half of which need to be Re-Generated because of changes to the generation process.
Based on my Map, I have placed assets in 27% of the total locations. (430, as of my last and probably outdated count)
Is there a Marco that could act like a sign up genius for calendar invites? It gets tiresome to deconflict calendars with multiple stakeholders. Would love to have something where they just sign up for an available time slot and then the calendar invite goes out to the respective groups and populates on the parent calendar.
I remember I yoinked a macro from this subreddit that automatically removes highlighted merged cells and replaces it with center across selection in one macro. I put it in my personal.xlsb and added a shortcut on my custom macros ribbon so I can do it with one click. Dunno who to credit but here is the code: Sub ConvertMergedCellsToCenterAcross() Dim c As Range Dim mergedRange As Range 'Check active sheet is a worksheet If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub 'Loop through all cells in Used range For Each c In ActiveSheet.UsedRange 'If merged and single row If c.MergeCells = True And c.MergeArea.Rows.Count = 1 Then 'Set variable for the merged range Set mergedRange = c.MergeArea 'Unmerge the cell and apply Centre Across Selection mergedRange.UnMerge mergedRange.HorizontalAlignment = xlCenterAcrossSelection End If Next End Sub
You are a god among mere men, good sir
Gotta spread the gospel of center across selection and kill the merge cells cult. I am merely but a humble messenger.
Damn that one is awesome
Whats the application for this?
Merged cells are a pain in the ass to work with. Center across selection makes selecting data much more painless but still keeps the aesthetic of centering headings. Here is a video example of what I'm talking about: https://www.youtube.com/shorts/nw43T1k7uI4. This will make much more sense. The video shows the manual way of undoing merge and replacing it with center across, it's the same thing as what the macro does.
Fixing other people’s poor formatting.
Makes all sheets 100% zoom and puts cursor on A1 and makes first sheet the active sheet. So clean, I love it.
Very simple to do as well, I like this one
I have made this macro before too but so seldomly needed it I often forgot and usually did it the manual way Select All Sheets > Zoom: 100% click on A1 (I think that set the cursor position for all sheets IRCC)
Here is the macro to do this for anyone interested. Auto sets all sheets at 100% zoom in your workbook and places the selected cell at A1 for every sheet as well, then goes to the first sheet after it's done. Sub SetZoomA1() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets ws.Activate ActiveWindow.Zoom = 100 Range("A1:A1").Select Next Application.ScreenUpdating = True Sheets(1).Select End Sub
I just had Claude work this up for me. Sub SetZoomAndCursorPosition() Dim ws As Worksheet ' Loop through all worksheets in the active workbook For Each ws In ActiveWorkbook.Worksheets ' Activate the current worksheet ws.Activate ' Set the zoom level to 100% ActiveWindow.Zoom = 100 ' Move the cursor to cell A1 Range("A1").Select Next ws ' Activate the first worksheet after the loop ActiveWorkbook.Worksheets(1).Activate End Sub
I have a macro that disables F1 when excel starts.
I have a screwdriver that disables F1 with each new keyboard lol
Feel free to share the code 👀 for a friend, of course 😁
Sub disableF1() Application.OnKey "{F1}", "" End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This was the first reason for me to start looking into macro's. Still haven't had the time to understand and implement them yet though. Would appreciate it if you could share the code!
Sub disableF1() Application.OnKey "{F1}", "" End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
ThisWorkbookOpen macro that uses Application.Speech to tell me a different joke everymorning.
I wrote a macro that reads a table into one or more collections (I know there is dictionaries but I don't need additional references for collections) so I can use the values on the worksheet as parameters instead of hard-coding parameters. This has the effect of not needing to edit code unless the algorithm has an issue.
I do a more basic version of that with a “control sheet” where users can update parameters according to their needs; but I’m just using named ranges > string variable usually. Table to collection is a Ferrari to my Honda
The point of using a collection is that when you read the table in you can put the keys into the collection so your variable becomes for example `srcshtcfg("Row Number")` for "Row Number" under `srcshtcfg` and your codes practically write themselves. I got this idea from those .ini files you see from practically everywhere.
Yes I just whipped this up for the first time to automate moving data from an RFQ log to a quote template and creating a file folder for that template in a directory, which also contains subfolders. The location of the template, the mapping of value X in log to sheet and range Y on the template, the directory for the new folder, the naming convention for the template copy and the folder, all the subfolders we want, are all listed in tables which load into variables or arrays. This way the user can modify the template and change the directory as time goes on, and just change the tables in the Setup sheet of the log, on and they don’t need me to change the code.
I’ve repurposed a sub that drafts an email and pulls a bunch of parameters into the body (e.g.: links to files, coding) at least a dozen times.
Yep, same. I shove an SAP report into Excel and it grabs data from excel and access to email out routine pdf reports to customers. 3 mins saves 2 hours.
So what T-Codes are you using this for? I’m just curious how you’re doing this. Every other day I’m getting an email for someone wanting me to send MB51 transactions to our Sales and Supply Chain colleges who have access to the same damn source. It would be nice to automate this.
Back when I was doing SAP auditing I automated pulling t-codes with the SAP GUI Scripting. Then built macros to pull the exported data into an excel sheet and start baselining the data for my review.
This is something I’ve been meaning to do for a report I refresh and email bits of 2x daily. The annoying part is offloading it to coworkers when I’m on PTO, they end up skipping more complicated steps to the detriment of production. So you can get it to create an email “template” on the desktop that can just be opened and hit send?
You can use .display to show you the email draft (which you then manually send), or .send to send it automatically without review.
I was hired to do simple data analytics with excel right after college. With the combination of VBA, Python, Bash and SQL I automated my job and got passive income for almost 3 years.
I’m in a similar boat. What happened after 3 years?
If it’s anything like my job in college, eventually the company finds a better paid developer intern (also in college) to make their database do the same thing automatically. The district managers will complain they don’t get the “raw data” but eventually come around and then I had to find a new job.
I found out I was about to have a baby so I started looking for better paying jobs and became a data engineer.
Actively working on doing this. Just started 6 months ago and have almost got it working the way I need it to. My job requires matching tables of pc hardware inventory and screws that are different measurements. It’s rather messy data that makes fuzzy matching work inconsistently.
Nice! It’s totally worth it.
I have one to clear all filters in a table. It's stupid that that needs a macro and isn't baked into Excel! I also have one that squirts an XLOOKUP into a cell (the parameters of the XLOOKUP are derived from another cell that will be in a fixed position relative to the cell from which the macro is called) then replaces the formula with the value. That's quite a specific use case, but I use it a lot. (Yes, it's a spreadsheet that should really be a database.)
Isn't clear all filters a button the data ribbon menu?
Yeah I put this button in Quick Access on any new install.
This is not available if the worksheet is locked.
Alt + A + C
muscle memory my left hand twitched just reading this
Perhaps look into `Worksheet.ShowAllData` in the object browser. Something along the lines of If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData Endif
Good stuff!
I have a macro that resizes all tables in a document to fit to page and sets them to the same style.
Can you share this macro please. I use lots of tables in weekly reports. Thank you
Sub Update_tables() ' Update_tables Macro Dim tbl As Table For Each tbl In ActiveDocument.Tables tbl.Style = "Grid Table 1 Light" tbl.AutoFitBehavior wdAutoFitContent tbl.AutoFitBehavior wdAutoFitWindow tbl.Rows.HeightRule = wdRowHeightAuto Next tbl End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Is this in Word? If yes, then please share.
I balance a GL and have to essentially add up everything that goes through it and add it to this other tracking sheet as debits and credits. A lot of the same stuff goes through it every single day and is labeled the same thing, so I have a macro that does a SUMIF for all those regular things and throws it at the bottom of the data for me to copy/paste where I need it on the other GL tracking sheet. Then, I have to work another page in that same process that's essentially just collecting numbers that are always in the same cells. Macro grabs all those numbers and puts them into a nice order for me to once again copy them over to my GL tracking sheet. Idk if it's my favorite, but it's definitely one of my most used.
And once again I am coming here to tell you about the ways of LibFileTools! Lol "getlocalpath" changed everything for me. We are using SharePoint/ OneDrive and I basically export and import everything 100% automated with ADO DB, so getting a local path that is auto generated for every user was a must. I will die the day the tool isnt getting updates and out of service (there are a lot of SharePoint/ OneDrive updates, so you have to update the module often). [LibFileTools ](https://github.com/cristianbuse/VBA-FileTools)
I’ve used =cell(“filename”) for basic file path. I’m guessing this does more with sharepoint and multiple users?
The problem is that the SharePoint on the local drive is located within the users directory... So the path can't be hardcoded if the file is used by more than one user. I would be surprised if your approach works, haven't tested it tho Users / Ewgrooss / OneDrive / Files
I usually just use it to find file locations for files people link me. Which means you only need to look at a few of the pieces in the file path to learn the location. The link would never work for a different user.
Just tested it out. It uses the SharePoint root rather than user. I'm going to start using this from now on haha
There was a table of data we used on a website with no export function. I wrote a macro to load the table in an invisible internet explorer window, then loop through the table and put it into an excel report that then created an email in outlook with a synopsis of the data and the full table data worksheet as an attachment. Since it was a report management wanted hourly it saved nearly half of an FTE and minimized mistakes. I was really proud of it.
Was the table behind a login? We have two portals that I could make use of scraping daily or on command but they’re behind logins and I’m not sure if that can be handled.
Its been a few years, but I believe the login was tied to the windows user account so I didnt need to solve for it. Id imagine you could still do it with a login, if you didnt feel comfortable hard coding the login info maybe you could set the ie instance to visible and log in first before the data was grabbed by the script.
Resetting UsedRange is by far my most frequently used macro. It's absurd how often I'll find colleagues made workbooks that have UsedRange extend out to column XFD with data only to like column AA. Or tables that encompass the whole column instead of just the 200 rows used. Quick way to reduce file size by ~2MB :)) ``` Sub UsedRange_Reset() Application.ScreenUpdating = False With ActiveSheet uRLastCol = .UsedRange.Columns.Count uRLastRow = .UsedRange.Rows.Count For r = uRLastRow to 1 Step -1 If Application.WorksheetFunction.CountA(.Rows(r)) <> 0 Then If r <> uRLastRow Then .Range(.Rows(r+1), .Rows(uRLastRow)).EntireRow.Delete Exit For End If Next For c = uRLastCol to 1 Step -1 If Application.WorksheetFunction.CountA(.Columns(c)) <> 0 Then If c <> uRLastCol Then .Range(.Columns(c+1), .Columns(uRLastCol)).EntireColumn.Delete Exit For End If Next Application.ScreenUpdating = True uRLastCol = .UsedRange.Columns.Count uRLastRow = .UsedRange.Rows.Count End With End Sub ``` Have to add a disclaimer though, this code assumes used columns and rows have values attached. Meaning any purely decorative columns/rows at the edges of your values will be deleted.
I can select a column of names and it makes a folder with each name
Also not fancy, but just about every macro I write includes a file select and data import sub. Copy paste bad!
I just use a formula to define a predictable file name instead. I don't have enough time of my day to Click things. For troubleshooting I can always set the dates or change the parameters which generated the filename.
Agree for output files, I also use formulas, usually with the current date at the end. Many of my vba use cases have been to take an input file(s), qc/manipulate/append info, and generate an output file for the next step in a process (I work in fundraising). For the first step, I find file dialog boxes to be intuitive for my users.
Why not the date at the front? Automatically sorted by date if you format it YY(YY)MMDD
You might get some interesting answers at r/VBA. I'm not sure about "favourite" but I get a lot of repeat use out of my [Dictionary](https://github.com/sslinky/vba-extendeddictionary) and [List](https://github.com/sslinky/vba-list) objects.
Not as fancy as others but I have one that formats numbers to accounting with no decimal places. It’s simple but being able to press CTRL+shift+K for it is a nice QoL upgrade
it paints the rows of selected range as if it is a table. i like the alternating colors of the tables but i don't want to use them allways. Public Sub TablePaint() If Selection.SpecialCells(xlCellTypeVisible).Cells(1, 1).Interior.Color = 16777215 Then 'empty Call Application.Dialogs(xlDialogEditColor).Show(1, 226, 239, 218) Else With Selection.SpecialCells(xlCellTypeVisible).Cells(1, 1).Interior Call Application.Dialogs(xlDialogEditColor).Show(1, _ .Color Mod 256, (.Color \ 256) Mod 256, .Color \ 65536) End With End If Dim anArea As Variant Dim aRow As Range Dim i As Integer For Each anArea In Selection.Areas i = 0 For Each aRow In anArea.SpecialCells(xlCellTypeVisible).Rows i = i + 1 If i Mod 2 = 1 Then aRow.Interior.Color = Application.ActiveWorkbook.Colors(1) Else 'Second Rows End If Next aRow Next anArea End Sub
I use something similar, but a lot of my data has paired rows through a a common identifier. So when I run it, it asks what column contains the identifier (it's different in a lot of cases) and then uses that to only paint the row if it's different from the identifier on the previous row. Makes it a LOT slower than yours, but I love the color selection you use, might incorporate that into mine!
i use similar, however i prompt the user to select the alternating colors they like to use. Dim color1 As Long, color2 As Long Dim lcolor As Long ' Check if a range is selected If TypeName(Selection) <> "Range" Then MsgBox "Please select a range first." Exit Sub End If ' Use the currently selected range Set rngSelect = Selection ' Prompt user to select first color MsgBox "Please select the first color." If Application.Dialogs(xlDialogEditColor).Show(12) = True Then ' User pressed OK color1 = ActiveWorkbook.Colors(12) Else ' User pressed Cancel color1 = xlNone End If ' Prompt user to select second color MsgBox "Please select the second color." If Application.Dialogs(xlDialogEditColor).Show(12) = True Then ' User pressed OK color2 = ActiveWorkbook.Colors(12) Else ' User pressed Cancel color2 = xlNone End If i = 1 ' Loop through each row in the selected range For i = 1 To rngSelect.Rows.Count ' Alternate colors for odd and even rows If i Mod 2 = 0 Then rngSelect.Rows(i).Interior.Color = color2 Else rngSelect.Rows(i).Interior.Color = color1 End If Next i
I wrote one that breaks links in the spreadsheet so it can go out to others without the ability to refresh
I can't claim credit, but I found a macro that deleted every empty row. Very handy to clean up data.
Can you share please
Sub DeleteRows() Dim x As Long With ActiveSheet For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1 If WorksheetFunction.CountA(.Rows(x)) = 0 Then ActiveSheet.Rows(x).Delete End If Next End With End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Wrote a ConcatSelected that when a range of cells are selected, then macro button pressed, a user form shows with a textbox containing all the cells joined like 'a','b','c' so I can copy paste the csv list into a SQL in() clause
I love this.
I have a couple of month-end reports that need to be updated with data dumps from a consolidation software we use with an Excel add-in. I wrote a macro to clear the existing pasted data dumps from the report, open and refresh each of the three data dump Excel files from the add-in, paste that data back into the report, and update month headers and column references for the new month end. Usually takes an hour to update everything manually. With the macro, it shaves off maybe 15 minutes, but more importantly I can fuck off and do something more productive while it does the boring stuff for me. I used to calculate commissions for a couple dozen sales reps. The sales data came from SQL via PowerPivot into a pivot table, but had to be reformatted and cleaned up to the style that the sales team wanted the reps to see their sales. Also had to create the file for the payroll department to load into the payroll software so that the reps could actually be paid and put all of the commissions information into an email to send off for approval. Total process took about 3 hours every other week. My macro saved more time on this one, shaved it down to a 5 minute process with the click of a button.
Macro that opens up list of sheets in workbook that I can use to jump to specific sheets
Perhaps a lesser known aspect of Excel is that this is built in, you can right-click on the arrows to the right of the first sheet. https://preview.redd.it/zgrnyogxuixc1.png?width=503&format=png&auto=webp&s=3cb576801ed6dced9475d9d7bf029c399870ee08
Yeah no, I assigned the macro to ctrl + shift + x, it populates a window with all the sheets in my workbook, then I can use my keyboard to select which sheet I want to jump to. No using my mouse and it’s 20x faster if my workbook has a lot of sheets. Edit* actually it seems like we’re talking about the same thing, except my macro means no clicking.
Can you share the macro please?
A significant portion of my job involves sending regular reports to other areas of my company that need to be updated with new data every month, quarter, etc. The actual files don't need to be changed much other than for the new data. Before I took over these reports, the way these updates were handled varied from file to file. Some would require a lot of maintenance to adjust formulas for larger datasets, copy-pasting data from database output, making new sheets based on a template. Now, I've got a pretty standardized pipeline of how these reports are updated. Data is queried through a data connection directly into the files, and formulas are set up to use dynamic array calculation or table calculation to populate with new data. All that was needed after that were macros to update each report from one instance to the next. And finally, I have a wrapper file that will open each report I list, run the update macro in each, close the file, and move on to the next. So I have a chain of macros that, with a click of one button, will update each report I desire.
For each person in a filter: - select the person - print to PDF - for generate an email
Copy paste values to remove formulas.
I had my macro open all my reports, update them, save them as a pdf and then print them.
I've got several I use in almost every sheet. Function splittext(ByVal SplitThis As String, ByVal SplitPoint As String, ByVal index As Integer) As String Dim LArray() As String Dim ArrayLen As Integer LArray = Split(SplitThis, SplitPoint) ArrayLen = UBound(LArray) - LBound(LArray) If index > ArrayLen Then splittext = "" Else splittext = LArray(index - 1) End If End Function Splits the text string at each space and returns the second text group, which would be "bbb" Function revfind(ByVal FindThis As String, ByVal InThat As String, Optional ByVal InstanceNum As Integer) As Integer Dim NumFound As Integer Dim That As String Dim CompareStr As String Dim Answer As Integer FindLen = Len(FindThis) ThatLen = Len(InThat) If InstanceNum = 0 Then InstanceNum = 1 End If That = InThat Do While ThatLen > 0 CompareStr = Right(That, FindLen) If FindThis = CompareStr Then NumFound = NumFound + 1 End If If NumFound = InstanceNum Then revfind = ThatLen ThatLen = 0 Else ThatLen = ThatLen - 1 That = Left(That, ThatLen) End If Loop End Function revfind works just like find, but it works right to left instead of left to right. Sub sheeton() If Application.Calculation <> xlAutomatic Then Application.Calculation = xlAutomatic End If If False = Application.ScreenUpdating Then Application.ScreenUpdating = True End If If False = Application.EnableEvents Then Application.EnableEvents = True End If End Sub Sub sheetoff() If Application.Calculation <> xlManual Then Application.Calculation = xlManual End If If True = Application.ScreenUpdating Then Application.ScreenUpdating = False End If If True = Application.EnableEvents Then Application.EnableEvents = False End If End Sub These two turn off most automatic calculations that a worksheet does. You use these by running SheetOff before you run a long macro of your own and then running SheetOn at the end of your Macro. This allows your Macro to run at a higher speed and only recalculates anything and updates the screen at the end of your Macro's run, which can speed up long macros.
Sub HideRows(ThisSheet As String) Dim ColToRead as Integer Dim FirstRow as Integer Dim LastRow as Integer ColToRead = 1 FirstRow = 3 LastRow = 4000 Call sheetoff For HiddenRow = FirstRow To LastRow RowValue = Worksheets(ThisSheet).Cells(HiddenRow, ColToRead).Value If False = RowValue Then 'If row is supposed to be Hidden If False = Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden Then Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden = True End If Else If True = Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden Then Worksheets(ThisSheet).Rows(HiddenRow).EntireRow.Hidden = False End If End If Next HiddenRow Call sheeton End Sub This reads the value in column A (ColToRead = 1) on rows 3 to 4000 (FirstRow and LastRow). If True the entire row is set to be visible. If false the row is set to be hidden. Passes in the name of the worksheet as a check. If called from a Worksheet\_Change(ByVal Target As Range) you can have the sheet dynamically hide and show rows as information changes in the sheet.
Macro that unhides all worksheets: `Sub Unhide_All_Worksheets()` `'Unhide all worksheets in this workbook.` `Dim ws As Worksheet` `For Each ws In ActiveWorkbook.Worksheets` `ws.Visible = xlSheetVisible` `Next ws` `End Sub`
I've recently made one that's for converting OCR text form a .pdf where it dumps everything in column A instead of the origin table format. You just type in the number of columns and run line by line. You could easily set it to do the whole file, but in my OCR experience, there are specs and spots that will wind up as an extra value of . or , in a row and mess up the offset. Sub LoopShift() Dim n As Integer Dim s As Integer Dim t As Integer Dim m As String Dim o As Integer Dim sCell As Range Dim tCell As Range 'n is the number ofcells down to shift over (ex. data range A1:A7 = 6 cells that need to be shifted) n = 6 For s = 1 To n t = 1 - s 'Move cells Set sCell = ActiveCell.Offset(1, 0) Set tCell = ActiveCell.Offset(t, s) sCell.Activate Selection.Cut Destination:=tCell Next s 'Delete blank rows m = 1 & ":" & n o = 1 - n ActiveCell.Offset(o, 0).Rows(m).EntireRow.Select ActiveCell.Activate Selection.Delete Shift:=xlUp ActiveCell.Select End Sub
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[DB](/r/Excel/comments/1cgd7qv/stub/l1vcfie "Last usage")|[Returns the depreciation of an asset for a specified period by using the fixed-declining balance method](https://support.microsoft.com/en-us/office/db-function-354e7d28-5f93-4ff1-8a52-eb4ee549d9d7)| |[SUMIF](/r/Excel/comments/1cgd7qv/stub/l1vibto "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[TEXTJOIN](/r/Excel/comments/1cgd7qv/stub/l1zam3x "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[XLOOKUP](/r/Excel/comments/1cgd7qv/stub/l1v2ny5 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cnps2z)^( has 18 acronyms.) ^([Thread #33030 for this sub, first seen 30th Apr 2024, 01:27]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I deal with a spreadsheet with ~70 columns and I only need a dozen. I have a macro that hides what I don't want to see, adds a hyperlink, color codes some column values based on other columns or date ranges. I color code my customers to stand out and for filtering. Column width adjustments and a few other things. I use it every day.
CTR+F but for columns and cells.
[удалено]
fyi, reddit spam filters blocked that link and removed your comment
Is there a macro to change all $values in a column to regular numbers but round to the nearest hundredths.
Turbo TTS. 🙌
I have a list of ink colors with their approximate hexadecimal color code. I made a macro that colors the cell according to the hex code it contains, and also converts it to HSV in the next three columns so I can sort by hue.
Select only visible within a range. So when you have a pin autofilter on some data, you can select and format only those that haven’t been filtered out
You can also use Alt+; (semicolon) to select visible cells only within a highlighted range. Big game changer when I found out this shortcut embarrassingly recently
My favourite one is one I use for work: it takes data copies from a notepad file (tab delimited), parses it, displays it all in a table format, then cross references that with another sheet (in the same book) which has a list of tickets and displays all of the ticket data on our system (like open/closed, assigned to, etc), then turns it into an actual table, and then displays a bunch of stats at the bottom of the table and makes sure to name all of the cells with stats in them. Lastly, it then updates a few other separate sheets with weekly and monthly stats. Before I got there, they were doing all of this *manually*. It took them hours, if not most of the day, and it was a collaborative team effort. Now it takes 5 minutes a day!
Linear interpolation module.
Super simple: the Evanesco macro: Range(***).ClearContents
Does anyone have one that turns a data input separated by commas into separate cells? It's a daily part of my workload at my new job (importing from SQL) and requires fiddling with the menus every time. I tried recording a macro for it but it didn't restore work.
This should work (I used the macro recorder so some parameters might be unnecessary) it assumes you have the data selected and that you want the results to start in cell A1 which will overwrite any data already in there without the ability to Ctrl+Z/Undo: Sub commaDelimitedToCols() 'Takes current selection of comma-delimited data and converts it to columns Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, Semicolon:=False, Tab:=False, Space:=False, _ Other:= False, Comma:=True, TrailingMinusNumbers:=True End Sub
Thanks! Will give this a shot at work tomorrow morning 🫡
I have a combination of three navigational macros. The first lists the names of all the sheets on a Home Page, the second jumps to the name of the sheet listed in the cell, the third back to the home page. So, if you are working though a massive workbook you can use it to easily jump from one 'end' of the spreadsheet to the other. You can even just lay down a 'launch pad' by typing in the name of a sheet on the existing tab you are to jump straight to that one. PS Yes, I know I could use hyperlinks but who has time to use the mouse?!?
Protein
Macro and cheese.
I have a macro that will autofill formulas
The one I use the most, Protects/Unprotects all worksheets. I have it set up on a userform in case I want to add a password.
I use a bunch of them. Every workday, I download ten .csv tables of data and I need to organize them. So I have a macro that opens each table, pastes it into a worksheet that also applies the date. I have a macro that will delete hidden rows. I have one on a button on my Quick Access Toolbar to apply custom formatting such as when I want +0.0%/\[Red\]-0.0%/"flat". One of my faves is a very simple macro that replaces every 0 digit with a 0, 1 with a 1,... 9 with 9, effectively converting numbers stored as text to active numbers without having to use a helper column.
I probably use pasting a selection as text the most. If you have a bunch of part/serial numbers, Excel wants them as numbers. And, even if you change the formatting from General -> Text, Excel doesn't change the actual data unless it's re-entered. So, I have a macro that goes through the selected range and does that for me.
For macros, it's mostly about setting up sheets in a form that works, like I give it a chemical composition and it will set up a flash calculation. For functions, The most helpful for me are ones that reference tables of data. Say, for example, I need a molecular weight for a chemical, I keep all the physical properties in a table in the PERSONAL excel file. That file opens at startup, so the table should always be available. All I need is =mw("dat chemical"). Obviously the code is like a simple vlookup, but I don't have to recall the name of the table, the columns to reference, etc. Other helpful ones are those that will look up physical property data needed for a calc. If I need density for water at 25 deg C, I can just call my function, it'll do the calc based on the tabulated constants. It's funny how people will see these things and think it's absolute wizardry. Nope, just a table and 3 lines of code.
Mine would be one that auto saves a pdf copy when I save a spreadsheet if I could ever get the damn thing to work. Everyone I’ve asked says the code looks right but it won’t work
Email macros that send hundreds of emails with dynamic attachments and bodies.
Just a macro to shortcut Paste only values with Ctrl +S
I have been using Excel to do Worldbuilding for a project for quite a while. My most frequently used macro is one that launches a very complex chain of UserForms. Based on if i use Ctrl+g (Auto-Complete) or Ctrl+Shift+g (Manual-Complete); it goes through launching around 20 unique UserForms which are each designed to do a specific task. Depending on the size and complexity of the initial state, it can take anywhere from 5 to 30 seconds to fully run. It is not super optimized, but it doesn't really need to be. All in, I Probably have upwards of >1000 hours of work into it.
Might be a stupid question but what’s a UserForm? Also, if you wouldn’t mind telling, what is the project?
#UserForms UserForms are customizable Prompt Windows. You add them the same way you can add a new Module and they basically do two things. First, Sub Initialize runs, and you can use that to call whatever functions you want to populate the various elements.
Second, it enters a waiting loop, which is basically just listening for clicks, key-presses, and other 'Events'. As an example, if you add a text box, it will call Textbox1.Click(). If you Type into Textbox1, then Textbox1.Change() is called. You can modify what happens when the elements are interacted with in about any way as you can conceive in programming.
Another example could be clicking a button. Lots of things you can do there too. I usually add a Frame (basically a 'Container Window') with 'Accept' & 'Cancel' buttons.
By default, every UserForm has an 'X' button in the top right, which calls .QuearyClose(). It is also triggered when you use the 'Unload Me' command in your programming. I usually add that to any actions that I want to destroy the UserForm Object from memory.
Most of the time, you don't really want to destroy the object unless you are done with it, but for my specific use case, it would be a lot more difficult to get the behaviors I want from a window that stays loaded.
Assuming you have a Module to write Macros in, you must add a Sub to create, manage, and destroy your UserForm. They can all be in the same Module, or in different ones, as you see fit.
As an example, in Module1:
`Sub ActivateUserForm1()
Dim UF1 As UserForm1
Set UF1 = New UserForm1
UF1.Show
End Sub`
(If you don't use 'Unload Me' in your code somewhere, you can use 'Unload UserForm1' after UF1.Show, and it will be called after QuearyClose is called.)
Now, you can bind that to a command key in the Macros Window > select your macro > options, or bind it to a Shape you can use as a Button by right clicking it and selecting 'Assign Macro'.
#Project
As for the project itself, it is a Procedural Generator for assets used in my DnD campaign. It builds the Skeleton of all the places for me so that I can have a randomized and varied scaffolding to start with.
You can see r/Avastworldbuilding for more information.
I just did a quick count, and I have 30 Sheets of 'purposeful information' so that doesn't include the directory, examples, templates, etc.. My main sheet, Autogen, is down to Row 10,396. I have 116 individual Assets, about half of which need to be Re-Generated because of changes to the generation process. Based on my Map, I have placed assets in 27% of the total locations. (430, as of my last and probably outdated count)
Allowing multiple selections from a picklist.
[удалено]
More VBA than a macro but I run it like a macro
Is there a Marco that could act like a sign up genius for calendar invites? It gets tiresome to deconflict calendars with multiple stakeholders. Would love to have something where they just sign up for an available time slot and then the calendar invite goes out to the respective groups and populates on the parent calendar.